August 27, 2009 at 7:03 am
Hi All,
I am having some confusion over something I have read regarding clustered indexes and the table data and I am hoping someone can help.
I have read (all from the MCTS 70-431 book) that the number of rows at leaf page level is dependant on the data type of the clustered indexed columns i.e. if you have an index on a column in a table that is char(60) you can have 134 rows of data in the leaf level page (134*60=8040). I have also read that the leaf level of a clustered index is the actual data within the table.
If the clustered index is the actual data should the index size not be the total size of the row, not just the clustered index column? By this I mean, if you had 2 columns in a table char(60) clustered index, and another column of char(60) should the number of rows in the leaf level page(if it is the actual data in the table) be (8060/(60*2)) or max of 67 rows?
Is the clustered index at leaf level only the indexed column data or all the data in the row?
Thanks in advance.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
August 27, 2009 at 7:16 am
my understanding is that clustered index only has column data not the row.... else if the whole row could be fit into a clustered index then Row by Agonising Row, the entire table would be clustered .... and no need for non-clustered indexes
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 27, 2009 at 7:23 am
Ness (8/27/2009)
I have read (all from the MCTS 70-431 book) that the number of rows at leaf page level is dependant on the data type of the clustered indexed columns i.e. if you have an index on a column in a table that is char(60) you can have 134 rows of data in the leaf level page (134*60=8040). I have also read that the leaf level of a clustered index is the actual data within the table.
No, it's dependent of the total size of the row (excluding overflow data, excluding LOB data) including row headers. For noncustered indexes that statement is partially true (only partially, because it's forgetting about include columns and row header)
If the clustered index is the actual data should the index size not be the total size of the row, not just the clustered index column? By this I mean, if you had 2 columns in a table char(60) clustered index, and another column of char(60) should the number of rows in the leaf level page(if it is the actual data in the table) be (8060/(60*2)) or max of 67 rows?
It'll be a bit less than that. There's row headers and a slot index to consider. Slot index is 2 bytes per row, header is, I think, at last 7 bytes.
So for a table with 2 char(60) columns, there will be max 8060/(60*2+9) = 62 rows (rounded down)
Is the clustered index at leaf level only the indexed column data or all the data in the row?
The clustered index has the actual data row in the leaf page. That's what makes it the clustered index, that's the main difference between clustered and non-clustered.
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
August 27, 2009 at 7:25 am
Amit Singh (8/27/2009)
my understanding is that clustered index only has column data not the row
The leaf level has the entire row, the non-leaf levels have just the clustering key
.... else if the whole row could be fit into a clustered index then Row by Agonising Row, the entire table would be clustered .... and no need for non-clustered indexes
Huh, don't understand what you're saying here.
The clustered index is the table. That's why there's only one of them per table.
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
August 27, 2009 at 8:54 am
hmmm ... looks like i need to revise indexing 101 🙁
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 27, 2009 at 9:09 am
You me and the MSCTS book!
Thank you for the replies - much appreciated.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
August 27, 2009 at 9:42 am
One more question - If the clusterted index is moved onto another filegroup, and therefore the table data along with it, is it just the leaf level pages that are moved or all of the root and intermdiate pages as well?
Thanks in advance
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
August 27, 2009 at 9:45 am
Anytime an index is moved to another filegroup all the pages that comprise that index are moved, root, intermediate ane leaf levels. This applies to clustered and nonclustered indexes alike
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
August 27, 2009 at 9:50 am
Many thanks
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply