December 8, 2010 at 5:49 pm
Given this table
[font="Courier New"]Create table t1 (c1 int not null, c2 int not null, constraint pk1 primary key clustered (c1))
[/font]
Is there any reason to create a non clustered index that includes as a key column the PK key column c1 like this:
[font="Courier New"]create nonclustered index x2 on t1 (c2, c1)
[/font]Or is this index entirely equivalent, given that c1 is already stored in the leaf pages?
[font="Courier New"]create nonclustered index x3 on t1 (c2)
[/font]
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?
December 8, 2010 at 10:46 pm
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:[font="Courier New"]create nonclustered index x2 on t1 (c2, c1)
[/font]Or is this index entirely equivalent, given that c1 is already stored in the leaf pages?
[font="Courier New"]create nonclustered index x3 on t1 (c2)
[/font]
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply