SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Design


Index Design

Author
Message
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2072 Visits: 2931
Hi All

After reading a few articles on Clustered/NonClustered Index design, I want to comfirm my understanding on the topic and make sure I'm not missing anything.

Clustered: The leaf level of the Clustered index is the actual data rows of the table, sorted Logically in a way that makes it easy for SQL Server to find.

NonClustered: The leaf leaf level of the Nonclustered index contains the data rows of the Column(s) specified in the Index definition, along with that, it also contains a pointer to the where the rest of the row resides. It also keeps the data rows of the Clustering key(The column(s) specified in the Clustered Index definition).

Am I missing anything here?

Thanks
Suresh B.
Suresh B.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 5326
What you have written about NonClustered Index in confusing to me. So instead of commenting on that, I would ask you a question to encourage to do further research on this topic:

What does NonClustered Index contain if the table is a heap?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86138 Visits: 45229
SQLSACT (8/24/2012)
Am I missing anything here?


Mainly that the pointer to the rest of the row is the clustered index key (if the base table is a clustered index). Not two separate things.

Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/

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


SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2072 Visits: 2931
GilaMonster (8/24/2012)
SQLSACT (8/24/2012)
Am I missing anything here?


Mainly that the pointer to the rest of the row is the clustered index key (if the base table is a clustered index). Not two separate things.

Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/


Thanks

Help me understand something regarding the Leaf Level of the nonclustered Index

Consider the following
--Table
create table test
(Col1 int primary key clustered
, Col2 int
, Col3 int)

--Index
create nonclustered index NCX on test
(Col3)

--Query
select Col3 from test where Col3 = '50'



Would my select statement still have an interaction with the base table even though the nonclustered index satisfies the query completely?


Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86138 Visits: 45229
If the index satisfies the query completely, why would SQL need to go to the base table?

Please read the articles I referenced.

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


Suresh B.
Suresh B.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 5326
SQLSACT (8/24/2012)

Would my select statement still have an interaction with the base table even though the nonclustered index satisfies the query completely?

No. Such indexes are called Covering Indexes.
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2072 Visits: 2931
GilaMonster (8/24/2012)
If the index satisfies the query completely, why would SQL need to go to the base table?

Please read the articles I referenced.


I'm confused about your first reply
Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level


If the index satisfies the query completely and a NonClustered index doesn't contain the data rows at its leaf level. Surely then it must go to the base table?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86138 Visits: 45229
No, what would it need to go to the base table for?
The only column you're referencing in your query in Col3, and that's in the index row at the leaf of the nonclustered index.

Please read the articles I referenced.

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


SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2072 Visits: 2931
GilaMonster (8/24/2012)
No, what would it need to go to the base table for?
The only column you're referencing in your query in Col3, and that's in the index row at the leaf of the nonclustered index.

Please read the articles I referenced.


Thanks

I think I'm getting lost in the jargon with this topic

Clustered Index Leaf level - Contains data rows

NonClustered Index Leaf level - Contains Index rows

I'm struggling to understand the difference between the Data Rows and Index Row

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86138 Visits: 45229
Data row = that which is found in a table (heap or clustered index)
Index row = that which is found in nonclustered indexes.

There are some differences in their details, but that's not important.

You say "Data row" and people will think you mean a row with all the columns that the table has. That is not what is in nonclustered indexes. They have just the index key, the pointer to the data row and any include columns.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search