Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to SQL Server Indexes: Step 10, Index Internal Structure Expand / Collapse
Author
Message
Posted Monday, March 28, 2011 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 10, Index Internal Structure
Post #1085227
Posted Tuesday, April 5, 2011 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 12:02 PM
Points: 5, Visits: 131
Enjoying the series. Looking forward to the rest of them. Hurry hurry lol.
Post #1088754
Posted Tuesday, April 5, 2011 11:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Am dancing as fast as I can.

Hope to have it all into Kalen (the editor) shortly.

Dave.
Post #1088812
Posted Thursday, May 19, 2011 2:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:06 AM
Points: 3, Visits: 54
Thank you very much! Finally finished going through all 10 steps little by little and been worth every second! Looking forward to the rest of the them.
Post #1112053
Posted Friday, January 20, 2012 1:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
Just one correction:

Non-leaf level entries contain only index key columns and the pointer to a lower level page. Included columns only exist in the leaf level entries; they are not carried in the non-leaf level entries.


Non-leaf entries of a nonclustered index contain the index key columns, a pointer to the page below and, if the index has not been declared as unique, it will also contain the clustered index key or RID (depending whether the base table is a heap or clustered index). This ensures that there are never complete duplicate rows in the upper level of a nonclustered index



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1239230
Posted Friday, January 20, 2012 6:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
GilaMonster (1/20/2012)
Just one correction:

Non-leaf level entries contain only index key columns and the pointer to a lower level page. Included columns only exist in the leaf level entries; they are not carried in the non-leaf level entries.


Non-leaf entries of a nonclustered index contain the index key columns, a pointer to the page below and, if the index has not been declared as unique, it will also contain the clustered index key or RID (depending whether the base table is a heap or clustered index). This ensures that there are never complete duplicate rows in the upper level of a nonclustered index


Interesting! Whats the downside of having duplicate rows in nonleaf nodes? Aren't they still going to point to the correct child nodes that they're the parent of?
Post #1239400
Posted Friday, January 20, 2012 7:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
patrickmcginnis59 (1/20/2012)
Whats the downside of having duplicate rows in nonleaf nodes? Aren't they still going to point to the correct child nodes that they're the parent of?


Yes, they will, however it can be more work when modifying or deleting the rows. Imagine an index on gender and a row is deleted. That row needs to be located in the nonclustered index to be removed (from the leaf). If all we had at the intermediate levels was the gender, SQL would have to scan half the index to find the row that it needs to remove. If the index rows are unique (from the clustered key or RID), SQL can just navigate straight to the row it need to remove.
http://technet.microsoft.com/en-us/sqlserver/gg508878.aspx (about 24 minutes in)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1239425
Posted Friday, January 20, 2012 7:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
GilaMonster (1/20/2012)
patrickmcginnis59 (1/20/2012)
Whats the downside of having duplicate rows in nonleaf nodes? Aren't they still going to point to the correct child nodes that they're the parent of?


Yes, they will, however it can be more work when modifying or deleting the rows. Imagine an index on gender and a row is deleted. That row needs to be located in the nonclustered index to be removed (from the leaf). If all we had at the intermediate levels was the gender, SQL would have to scan half the index to find the row that it needs to remove. If the index rows are unique (from the clustered key or RID), SQL can just navigate straight to the row it need to remove.
http://technet.microsoft.com/en-us/sqlserver/gg508878.aspx (about 24 minutes in)


Thanks! I didn't even consider deletes. I'll have to buy some popcorn and watch the movie!


Post #1239451
Posted Friday, January 20, 2012 7:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
patrickmcginnis59 (1/20/2012)
Thanks! I didn't even consider deletes. I'll have to buy some popcorn and watch the movie!


Not a movie. One of the MCM videos. That particular one is about 40 minutes long and all about index internals

p.s. not just deletes, any modification that has to change the data in the index leaf



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1239459
Posted Friday, January 20, 2012 2:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
There's just so much information in the this article and discussion!
Please keep up the good work.

Many books don't expose the level of detail presented here and leave us wondering how that really works from the inside.

Thank you very much David (and Gail for the additional info and link).


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1239755
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse