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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Indexing and hidden columns

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 EventID

Are the following secondary non-clustered indexes the same due to hidden column?

Example 1

Index key columns
EventID
UserID

Example 2

Index key columns
UserID

Example 3

Index key columns
EventID
Included Columns
UserID

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 2
Index key columns: UserID


NOTE: Having duplicate indexes will also cause slow performance


--
Chandu
Go to Top of Page

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.
Go to Top of Page

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 speak
can you elaborate what you mean by this? Did you mean leaf nodes contain actual data? if yes,you're correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 speak
can 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?
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -