November 18, 2010 at 8:36 am
Hello -- I'm trying to find out from an expert if I have interpreted information about how a clustered index works in conjunction with other indexes on the same table.
Basic example, there is an 'item master' table with a clustered index on the column 'itemnumber'
another column is on the table and it is an 'invtypeid'
in a couple of key applications the where clause is where a.invtypeid = @invtypeid and a.itemnumber = b.itemnumber
Currently the table only has the clustered index. I am going to add an index that is headed by 'invtypeid'
The way I understand the clustered index my new index will automatically have that column included.
so, would if be most efficient and work correctly if I just create the index with the column 'invtypeid' only?
leaving the 'itemnumber' implied?
Thank you for your input and experience.
November 18, 2010 at 8:47 am
That should work just fine.
Every non-clustered index includes the clustered index. That's how it knows what rows it's indexing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 9:09 am
Ellen-477471 (11/18/2010)
so, would if be most efficient and work correctly if I just create the index with the column 'invtypeid' only?leaving the 'itemnumber' implied?
Work correctly, yes. Most efficient, no. If you specify the clustered index column, SQL won't add it in a second time.
Personally, if I feel that a nonclustered index needs the clustering key in it, I will explicitly add that column into the nonclustered index. That way, should the clustered index be moved my index won't suddenly become less efficient, if I want to add another key column, the implied order of index columns doesn't get changed and so that it's clear to anyone reading what my intention was with the columns of this index.
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
November 18, 2010 at 9:39 am
The clustered index columns will be added as included rather than indexed.
If it's needed then I would include it explicitely.
If it's not needed and just there by default then I would omit it - unless I thought it might need it later and someone might not realise this index included it.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 9:46 am
nigelrivett (11/18/2010)
The clustered index columns will be added as included rather than indexed.
For a unique nonclustered index, the clustering key is present only at the leaf level (like an include column)
For a non-unique nonclustered index, the clustering key is present at all levels (like a key column)
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
November 18, 2010 at 9:47 am
Gail -- thank you. That's what I needed to know. I've never been certain if the optimizer added it twice. Or if the order changes.
Thank everyone else too that responded so quickly.
November 18, 2010 at 9:55 am
Ellen-477471 (11/18/2010)
I've never been certain if the optimizer added it twice. Or if the order changes.
SQL doesn't add it twice (and it's not done by the optimiser)
If the clustering key is implicitly added, it's added as the last key column(s) (if the nonclustered is not unique). That's the main reason why, if I need the clustering key as a key column, I will specify it so I can ensure the order of columns is correct.
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
November 18, 2010 at 9:56 am
could you point me to a succinct article that explains the difference [and how they are defined and used] between a column that is "included" in an index and a column that is a "key" ? I've always just thought about the columns in an index as the ones that are explicitly in the "create index" statement and then knew that the clustered index was included in all indexes [and I always assumed those columns were virtually added kind of like a pointer at the end of the columns designated in the "created" index ]
Thank you
November 18, 2010 at 10:15 am
Would one of mine do?
http://qa.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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply