NonClustered index on Clustered table

Question

NonClustered index on Clustered table

SQL*

SSCertifiable

Points: 6638
More actions
July 27, 2012 at 3:09 am

#392843

Hi All,
Today i got a very basic doubt when i am doing some R&D on Indexes.
Clustered: Will store actual data at the leaf level.
Non Clustered: will store the actual data of Index key and a clustered key which does pointing to the Clustered index.
1. If we create an index, internally this will be stored as B-Tree.
If i have created index on UserID (Integer) (Record count 100), What data the root/intermediate index pages will contain? by seeing the index structure in online (google) the index pages are showing only two values (1-50 at root and 1-49 one intermediate/50-100 in second intermediate page...soon).
2.What is the size of the index page? if the size is 8kb, if it stores only two values then the index pages size will be very small (what about the another 8kb-intsize*2).
3. If we have created a nonclustered index on Lastname column then the leaf level will contain actual data of Lastname and a clustered key which will be pointing to the clustered index.
for Ex: Lastname='James' his ID (on ID column we have clustered index) is 4, How sql server knows that the James Clustered key is 4?
what happened if we have multiple records with the same lastname 'James' and having different IDs?
🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 1

SQL Server has pointers that tell it which indexes refer to which records. Your James names with different IDs won't get lost. However, if you don't have your indexes written correctly, it is entirely possible that SQL will find the records by doing table scans instead of using the proper indexes.

And now it's time for the regular Indexing guru's (who know more about the physical bits) to make an appearance and fill in the gaps of everything else you said. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Brandie Tarvin (7/27/2012)
SQL Server has pointers that tell it which indexes refer to which records. Your James names with different IDs won't get lost. However, if you don't have your indexes written correctly, it is entirely possible that SQL will find the records by doing table scans instead of using the proper indexes.
And now it's time for the regular Indexing guru's (who know more about the physical bits) to make an appearance and fill in the gaps of everything else you said. @=)

I'm no guru but I know a good gapfiller when I see one, and this article by David Durant [/url]is an exceptionally good gapfiller.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jared SSC Guru Points: 61793 More actions · Answer 3

1. If we create an index, internally this will be stored as B-Tree.
If i have created index on UserID (Integer) (Record count 100), What data the root/intermediate index pages will contain? by seeing the index structure in online (google) the index pages are showing only two values (1-50 at root and 1-49 one intermediate/50-100 in second intermediate page...soon).

Is there a question here that I missed, or is this just a statement?

2.What is the size of the index page? if the size is 8kb, if it stores only two values then the index pages size will be very small (what about the another 8kb-intsize*2).

I believe that the size of the index page is 8kb, whether it is full or not.

3. If we have created a nonclustered index on Lastname column then the leaf level will contain actual data of Lastname and a clustered key which will be pointing to the clustered index.
for Ex: Lastname='James' his ID (on ID column we have clustered index) is 4, How sql server knows that the James Clustered key is 4?
what happened if we have multiple records with the same lastname 'James' and having different IDs?

SQL Server knows to link the clustered key with the NC key when the data is first inserted. When you insert a row into your table and a non-clustered index exists... The index has an update done to it. If your clustered index is not unique, then SQL Server adds a 4-byte integer to each row to make it unique. Read Gail's series on indexes starting here: http://www.sqlservercentral.com/articles/Indexing/68439/

Jared
CE - Microsoft