December 26, 2012 at 7:00 pm
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
December 26, 2012 at 8:46 pm
Nice explanation..
December 27, 2012 at 2:48 am
buyme22 (12/27/2012)
input this URL: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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 27, 2012 at 5:44 am
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
December 27, 2012 at 9:54 am
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.
December 27, 2012 at 10:17 am
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
December 27, 2012 at 10:30 am
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
December 27, 2012 at 10:33 am
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.
December 27, 2012 at 10:51 am
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 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply