Index Design

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Index Design

16 posts, Page 1 of 2

1 2 »»

Index Design

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Friday, August 24, 2012 12:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

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

Suresh B.

Posted Friday, August 24, 2012 3:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

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

GilaMonster

Posted Friday, August 24, 2012 3:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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

SQLSACT

Posted Friday, August 24, 2012 4:04 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

GilaMonster (8/24/2012)

SQLSACT (8/24/2012)

Am I missing anything here?

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

GilaMonster

Posted Friday, August 24, 2012 4:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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

Suresh B.

Posted Friday, August 24, 2012 4:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 1,075, Visits: 5,119

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

SQLSACT

Posted Friday, August 24, 2012 4:19 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

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

GilaMonster

Posted Friday, August 24, 2012 4:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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

SQLSACT

Posted Friday, August 24, 2012 4:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

GilaMonster (8/24/2012)

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

GilaMonster

Posted Friday, August 24, 2012 4:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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 »

16 posts, Page 1 of 2

1 2 »»

Permissions