SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Indexing and hidden columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 09/19/2013 :  22:14:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/20/2013 :  01:29:58  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 09/20/2013 :  16:48:29  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/22/2013 :  03:13:02  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 09/23/2013 :  12:20:23  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
425 Posts

Posted - 09/24/2013 :  12:32:09  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000