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

Non-clustered index keys contains clustered key column? Expand / Collapse
Author
Message
Posted Wednesday, December 8, 2010 5:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 31, 2014 8:35 AM
Points: 95, Visits: 578
Given this table
Create table t1 (c1 int not null, c2 int not null, constraint pk1 primary key clustered (c1))

Is there any reason to create a non clustered index that includes as a key column the PK key column c1 like this:
create nonclustered index x2 on t1 (c2, c1)
Or is this index entirely equivalent, given that c1 is already stored in the leaf pages?
create nonclustered index x3 on t1 (c2)

In the x3 index, is c1 also in the root and non-leaf pages? Is it considered with respect to sequencing the non-leaf pages?

Can you generalize and say the clustered key columns should never be part of the non-clustered index keys?







Post #1032239
Posted Wednesday, December 8, 2010 10:45 PM


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 @ 1:43 PM
Points: 40,411, Visits: 36,861
stevemc (12/8/2010)

Is there any reason to create a non clustered index that includes as a key column the PK key column c1 like this:
create nonclustered index x2 on t1 (c2, c1)
Or is this index entirely equivalent, given that c1 is already stored in the leaf pages?
create nonclustered index x3 on t1 (c2)


They are equivalent at the moment. Should someone at a later time change the clustered index key(s) or add another column to that index, they will no longer be equivalent.

In the x3 index, is c1 also in the root and non-leaf pages? Is it considered with respect to sequencing the non-leaf pages?

Leaf only if the nonclustered is unique, all pages if the nonclustered is not unique

Can you generalize and say the clustered key columns should never be part of the non-clustered index keys?


No, absolutely not. If the query that you're tuning is such that it needs the clustered key in the index (key or include), put it there. SQL's smart enough not to add it a second time.

If you don't explicitly specify the clustered key column, it's the last column of the index, which may not be what you want, you may need it elsewhere. It may be fine as the last column now, but later someone adds another column to the index and now the required order is wrong. Someone may at a later date change the clustered index, and now the nonclustered index is not as useful as it was.

If you need the clustered key within an index because of the query, explicitly add it. If you don't need it for the query, leave it out. Same as any other column in the table. Let SQL worry about what it's doing behind the scenes itself.



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 #1032300
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse