Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
 Posted - 2012-04-23 : 10:16:27
|
Hi all.Came across some tables today which had indexes like this:CREATE TABLE foo ( [fooID] INT , [fooVal] CHAR(50) , CONSTRAINT PK_foo PRIMARY KEY CLUSTERED ([fooID]) )CREATE INDEX IX_foo ON foo ([fooVal]) INCLUDE ([FooID]) (to simplify to the bare minimum.Basically a table with a clustered index and an index with an INCLUDED column for that CI.Now -- I think that all NONCLUSTERED indexes HAVE the CI in them (otherwise how would you use them to get any information - that's what a CI lookup *does*)I spoke to the other developer about it (we had a friendly argument). They said that the INCLUDE Was there for readability..... and he didn't think that SQL server would include the CI at the leaf node level because it would be smart enough not to.I didn't really like that argument but I didn't know for sure if that's the case or not.Anyone know if sql server will optimize the INCLUDE out? or will it just do what you told it to and include the info you told it to at the lead node level?I'd actually be happier if it just did the dumb thing because you'd told it to........Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-23 : 10:25:05
|
| Yes, the clustered index keys are always included at all levels of the index, including the leaf. Yes, if you explicitly add the clustering keys to a non-clustered index (or the included columns) SQL Server will NOT add them a second time. |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-04-23 : 10:32:51
|
| Cheers. I still *don't like it* but meh....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-23 : 11:14:11
|
| You don't like storing the data only once inside an index, instead of twice? Or you don't like him specifying the clustering key in the INCLUDE list?I agree that it's silly to include it, and could be detrimental if the clustering key should change in the future. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-23 : 11:50:19
|
quote:
Originally posted by Transact Charlie
Cheers. I still *don't like it* but meh....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
NC indexes have pointers to the clustered indexes at the leaf level.While in the covering indexes you have the columns which are covered by the query at the leaf level.So if you make a CI as a part of covering index so theoretically you are adding them at the leaf pages BUT sql server is smart.So as Rob said it wont add it second time.Unfortunately you would have to live with that.. After Monday and Tuesday even the calendar says W T F .... |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-04-23 : 17:16:55
|
quote:
Originally posted by robvolk
You don't like storing the data only once inside an index, instead of twice? Or you don't like him specifying the clustering key in the INCLUDE list?I agree that it's silly to include it, and could be detrimental if the clustering key should change in the future.
Oh I'm all for storing it only once. I don't like that it's specified in the INCLUDE list.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
|
|
|
|
|
INCLUDE PK in Non clustered INDEX?