indexes, when to use include, vs when to use key columns

Question

indexes, when to use include, vs when to use key columns

winston Smith

SSCoach

Points: 19500
More actions
August 24, 2011 at 8:47 am

#256075

Apart from the issue of size, which is addressed by included columns, does it matter if i place columns in the key, or in the include?
eg: i have a table with 3 columns:
col1 int - PK
col2 int
col3 int
if i create an index on Col1, Col2, Col3, when will that be used over a column with Col1 (include Col2, Col3)
Im thinking, from experience that i only use columns in the where or in the join predicate in the index key, and then any columns from that table in the select statement will be added to the "include" section of the index.
is this correct?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As a rather large simplification, yes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

winston Smith SSCoach Points: 19500 More actions · Answer 2

Thanks Gail! Now that my suspicions are confirmed I can expirement further.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i believe the key is the anticipated WHERE statement;

take this as an example:

SELECT

contactId,

FirstName,

LastName,

Email

From Contacts

Where Entitytblkey =77

the index's columns should match the WHERe statement, right? ENTITYTBLKEY is the only column, but by including the other 4 columns, I'd prevent teh idnex from having to go to teh table to get the data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Here's a nice intro: http://sqlblog.com/blogs/roman_rehak/archive/2007/05/03/why-use-included-columns-in-sql-server-2005.aspx

and of course good old BOL has usage info: http://msdn.microsoft.com/en-us/library/ms190806.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me