Index Design

Question

Index Design

SQLSACT

SSC-Insane

Points: 21838
More actions
August 24, 2012 at 12:14 am

#280776

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

Viewing 15 posts - 1 through 15 (of 15 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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 SSC-Insane Points: 21838 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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. SSC-Insane Points: 22986 More actions · Answer 5

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 SSC-Insane Points: 21838 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSC-Insane Points: 21838 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

GilaMonster (8/24/2012)
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.

Thanks - The Data/Index row is what had me

the pointer to the data row

This pointer is what is used when the Index doesn't cover the query and a Lookup to the Table/Clustered Index is required, right?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Yes, it's used to find the data row.

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 SSC-Insane Points: 21838 More actions · Answer 12

GilaMonster (8/24/2012)
Yes, it's used to find the data row.

Thanks Gail

SQLSACT SSC-Insane Points: 21838 More actions · Answer 13

GilaMonster (8/24/2012)
Yes, it's used to find the data row.

Thanks

I wanna run something by you that might be out of the scope of this thread, please just let me know.

I came across an article of yours on Simple-Talk

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

I want to make sure that I understand something correctly

If we have an Index that doesn't full satisfy a query, a Lookup to the Clustered Index/Table will then be done, ok.

Am I right in that this isn't always the case. Given that a Lookup can be expensive, SQL Server will first evaluate the data and will choose to do a Clustered Index/Table scan instead, under these circumstances:

>> Too many rows are being returned

>> The data is not unique enough

Are there any other circumstances where SQL Server will choose to do a Clustered Index/Table scan instead.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Have you read those three indexing articles yet? If not, please read them, especially the third one.

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