Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2009
    Posts
    18

    Question Unanswered: sql server indexing

    Hello all,

    i have some questions:

    1. what's the different between clustered and non clustered? i've got a lot of answer in google, but i wanna know from this forum.

    2. how non-clustered index works? (from not in index until get indexed. i know b-tree index, but i dont know how it works in SQL Server. different vendor, different implementation.)

    3. how non-clustered index works? (when insert query executed).

    ok, i will wait. thx for your expertise.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi kiman

    Do me a favour - could you tell me your answer to #1 and we can let you know how close you are. there's no point me repeating what is all over the web and in Books Online. Tell us your understanding and we can then refine it.

    for 2) - "from not in index until get indexed" - what does this mean? Also, SQL Server uses B-Tree structure. I believe most other vendors do too, but I suggest you ask in the specific forums if you need to know.
    3) You want to know what happens to the clustered index, and how the index is used, when data is inserted? Please also let us know if you are talking about single inserts or big batches.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    18
    hello pootle flump,

    wow.. nice...

    i think question number 1 is not important..

    for question number 2: i've got it from another forum..

    for question number 3: big batches (1000 rows). i want to know what happens to the non-clustered index in a table that already have non-clustered indexed on a column.

    i'm from indonesia, therefore, sorry if my english is bad hahaha...

    so i will wait again. Thx for your response...

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, 1 is pretty important. The best way to approach these questions is for you to give us your best guess and then we can point out if/where you're not quite right.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by kiman

    i think question number 1 is not important..
    What would make you think that?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kiman
    for question number 3: big batches (1000 rows). i want to know what happens to the non-clustered index in a table that already have non-clustered indexed on a column.
    There will be 1000 rows added to the leaf level of the non clustered index. The Root node and intermediate levels (if there are any) of the B-Tree may need to change to accommodate these new rows.

    If there is insufficient space on a given non clustered index leaf level page that requires a row inserting, the page will be split roughly 50-50. The page on the lowest level of the B-Tree (the level that contains references to the leaf level pages) that pointed to this page must have a new row inserted accounting for the split. If there is insufficient room on this B-Tree page to accommodate this new data, this page must also be split.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Posts
    18
    ok i got it.

    for question number 1: hahaha... sorry... i've already got the answer from another forum... and i've hit MSDN, so i know it now... if someone want to answer my first question, you can post it here.

    thx for your expertise.

    Regards,
    Wiwin.
    Last edited by kiman; 01-28-09 at 10:53.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or you could simply provide us with a link to where it's been answered already (this will benefit future readers)
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2009
    Posts
    18
    ok...

    Clustered VS Non-Clustered Index
    Code:
    http://www.mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm
    clustered index is optimized for non nulls and unique data.
    Last edited by kiman; 02-02-09 at 06:06.

  10. #10
    Join Date
    Jan 2009
    Posts
    18
    Dear Masters,

    I want to know, how to trace transaction? i want to trace insert query in an indexed column. Thx.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You want to see what happens? Press CTRL + L in the SSMS window containing the query. Observe the estimated execution plan. Press CTRL + M and then F5 and you will see the actual plan (these are unlikely to differ in this case).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2009
    Posts
    18
    o0o ok, i will try. Thx for your expertise...

  13. #13
    Join Date
    Dec 2008
    Posts
    59
    I have search it from the google hope this is very simple for you...
    A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

  14. #14
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by hkp819
    I have search it from the google hope this is very simple for you...
    A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
    YOU are the man.............Gotta love those "special" types of indexes. (I can be SO cynical sometimes!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •