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 |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-09-19 : 22:14:48
|
| I have a unique primary clustered key index on a table (Events, PK_EventID).Index Type: Clustered on EventIDAre the following secondary non-clustered indexes the same due to hidden column?Example 1Index key columnsEventIDUserIDExample 2Index key columnsUserIDExample 3Index key columnsEventIDIncluded ColumnsUserID |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-20 : 01:29:58
|
| As per my knowledge second example is enough to have non-clustered index...Example 2Index key columns: UserID NOTE: Having duplicate indexes will also cause slow performance--Chandu |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-09-20 : 16:48:29
|
| What do you mean by hidden columns?#2 is a good one, always a good practice to index an FK.I don't think #1 and #3 are helpful. I believe the clustered index already includes all columns so to speak. Anyone please correct me. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-22 : 03:13:02
|
| I believe the clustered index already includes all columns so to speakcan you elaborate what you mean by this? Did you mean leaf nodes contain actual data? if yes,you're correct------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-09-23 : 12:20:23
|
quote:
Originally posted by visakh16
I believe the clustered index already includes all columns so to speakcan you elaborate what you mean by this? Did you mean leaf nodes contain actual data? if yes,you're correct
Yes.And that that if you query on the column of the clustered index, there would never be a need for a Key Lookup.So I think that if you have a Clustered index on a table, there is no point in making any additional indexes beginning with that column. It's correct? |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-24 : 12:32:09
|
quote:
Are the following secondary non-clustered indexes the same due to hidden column?
No.The clustered index is keyed on EventID. #2 is keyed on UserID; EventID will be included in the index because it's part of the clus key, but it will not be part of the key. This index could be very useful.Indexes #1 and #3 aren't needed, since EventID is unique and is already the clustering key. |
|
|
|
|
|
|
Indexing and hidden columns