Can a Table Really Have a Clustered Index?

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    timothyawiseman (12/26/2012)


    Thank you for providing the article. It does provide a simple explanation for people new to the subject. But I think it should be pointed out (preferrably in the article) that you are simplifying a gret deal to make it easy to understand... your description is probably a fine place for beginners to start, but they should know that it is a simplification and not be surprised when they find out the reality is more complicated.

    Thanks for pointing this out. Unfortunately, I do not see any way to edit the article itself. But I guess I can say it here: my article does simplify what an index, specially a clustered index, is to help understand the concept. There is much more to it than ordered data. See the Stairways series on indexes on this website for further reading.

    Hakim Ali
    www.sqlzen.com

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Nice explanation..

  • Phil Parkin

    SSC Guru

    Points: 244736

    buyme22 (12/27/2012)


    input this URL:

    ( http://www.buy2me.net/ )

    you can find many cheap and high stuff

    Believe you will love it.

    WE ACCEPT CREDIT CARD /WESTERN UNION PAYMENT

    YOU MUST NOT MISS IT!!!;-):-D:-D

    Reported as spam.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    hakkie42 (12/24/2012)


    Ok, so in other words you're saying a clustered index means that the physical data is stored in index order, so e.g. binary searches are possible.

    Actually not. That's not how SQL works with indexes. The data is not necessarily physically ordered (logically, yes, physically no). There's no binary searches. SQL uses the upper levels of the b-tree (balanced tree, not binary tree) to navigate to the data.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dennis Q Miller

    Old Hand

    Points: 366

    hakim.ali (12/24/2012)


    Mr. hoek (12/24/2012)


    But why does the table allocation 'double' when adding a clustered index?

    The only explanation I can think of, is that SQL server has the information 'twice' in the database:

    - ones as pure 'table' data (all records/fields)

    - ones as sorted 'index' data (all records/fields)

    I'm not sure what you mean by table allocation doubling. I am sure that the database would not duplicate all data as 'pure' data and 'indexed' data. The clustered index causes re-arranging, not copying. A nonclustered index, however, will create a copy of the indexed fields along with pointers etc.

    The "doubling" you experienced is symptomatic of adding a clustered index with a 50 percent fill-factor to a table with little free space. When the clustered index is added, the data rows are sorted and become the leaf level of the index with the fill-factor applied. So, if there was not much free space to begin with, the allocated space can increase dramatically to accommodate the higher index levels and the newly requested freespace.

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Dennis Q Miller (12/27/2012)


    The "doubling" you experienced is symptomatic of adding a clustered index with a 50 percent fill-factor to a table with little free space. When the clustered index is added, the data rows are sorted and become the leaf level of the index with the fill-factor applied. So, if there was not much free space to begin with, the allocated space can increase dramatically to accommodate the higher index levels and the newly requested freespace.

    Just so I understand this right: are you saying that if you took a heap and added a clustered index to it with a fill factor of 5%, for arguments sake, the size of the table on disk would grow to 20 times the heap size (plus extra for non-leaf nodes)?

    Hakim Ali
    www.sqlzen.com

  • Gail Shaw

    SSC Guru

    Points: 1004484

    hakim.ali (12/27/2012)


    Just so I understand this right: are you saying that if you took a heap and added a clustered index to it with a fill factor of 5%, for arguments sake, the size of the table on disk would grow to 20 times the heap size (plus extra for non-leaf nodes)?

    Roughly correct, assuming that the heap had all its pages full. 5% fill factor means leave 95% of the page free space. Pages are 8k in size.

    Hence, if we had a table that was a heap and it was 64k in size (8 pages full), then if we were to add a clustered index with a 5% fill factor that means that SQL must put roughly 400 bytes on a page (assuming the rows are all smaller than that), so at 400 bytes per page (5% full), 64 k would take approximately 164 pages.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Robert Davis

    One Orange Chip

    Points: 28027

    Dennis Q Miller (12/27/2012)


    hakim.ali (12/24/2012)


    Mr. hoek (12/24/2012)


    But why does the table allocation 'double' when adding a clustered index?

    The only explanation I can think of, is that SQL server has the information 'twice' in the database:

    - ones as pure 'table' data (all records/fields)

    - ones as sorted 'index' data (all records/fields)

    I'm not sure what you mean by table allocation doubling. I am sure that the database would not duplicate all data as 'pure' data and 'indexed' data. The clustered index causes re-arranging, not copying. A nonclustered index, however, will create a copy of the indexed fields along with pointers etc.

    The "doubling" you experienced is symptomatic of adding a clustered index with a 50 percent fill-factor to a table with little free space. When the clustered index is added, the data rows are sorted and become the leaf level of the index with the fill-factor applied. So, if there was not much free space to begin with, the allocated space can increase dramatically to accommodate the higher index levels and the newly requested freespace.

    My suspicion is that the "doubling" referred to was a misunderstanding of the requirement to have 2.5 times the amount of space for creating the index.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Dennis Q Miller

    Old Hand

    Points: 366

    hakim.ali (12/27/2012)


    Dennis Q Miller (12/27/2012)


    The "doubling" you experienced is symptomatic of adding a clustered index with a 50 percent fill-factor to a table with little free space. When the clustered index is added, the data rows are sorted and become the leaf level of the index with the fill-factor applied. So, if there was not much free space to begin with, the allocated space can increase dramatically to accommodate the higher index levels and the newly requested freespace.

    Just so I understand this right: are you saying that if you took a heap and added a clustered index to it with a fill factor of 5%, for arguments sake, the size of the table on disk would grow to 20 times the heap size (plus extra for non-leaf nodes)?

    Yep, that's what I meant.

Viewing 9 posts - 46 through 54 (of 54 total)

You must be logged in to reply to this topic. Login to reply