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»»

Index Design Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 12:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1349477
Posted Friday, August 24, 2012 3:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
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?
Post #1349541
Posted Friday, August 24, 2012 3: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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1349542
Posted Friday, August 24, 2012 4:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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

Post #1349550
Posted Friday, August 24, 2012 4:09 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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1349552
Posted Friday, August 24, 2012 4:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
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.
Post #1349555
Posted Friday, August 24, 2012 4:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1349556
Posted Friday, August 24, 2012 4:28 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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1349561
Posted Friday, August 24, 2012 4:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1349567
Posted Friday, August 24, 2012 4:40 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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1349572
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse