Columns to be included in Index

Question

Columns to be included in Index

Krishna1

Hall of Fame

Points: 3666
More actions
March 27, 2013 at 11:26 pm

#270472

Dear All
I have one table with millions of rows, with columns as Id, stringcol1,stringcol2,Bigintcol3,numericcol4
Primary key is Id.
Index created on stringcol1
When I query this table ,
select sum(numericcol4), Bigintcol3
from abc
where stringcol1 = @stringcol1
and stringcol2 = @stringcol2
and Bigintcol3 in not null
group by Bigintcol3
It shows me to create index with columns as stringcol1,stringcol3,Bigintcol3 include numericcol4
If I exclude any column from the recommended index and check Estimated Plan it shows as index scan on Primary key
Does it means that in sql 2008 R2 we need to create indexes to include all the column in the “where clause” and also add filed of the select statement in the include column list ?
Regards
Krishna1

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Krishna1 (3/27/2013)
...
Does it means that in sql 2008 R2 we need to create indexes to include all the column in the “where clause” and also add filed of the select statement in the include column list ?

Not only in SQL Server 2008R2, but also in other editions 😉

The suggested index is a covering index. It is designed specifically for your query and takes into account every column in the WHERE clause (the filtering columns) and it includes the column from the SELECT clause, so a lookup doesn't need to be done to retrieve numericcolumn4 from the pages.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

INCLUDED columns were introduced in SQL Server 2005

They can improve performance by avoiding key look ups as the necessary columns are included along with the key columns in the indexes

You can check the below mentioned link for more information on INCLUDED columns

http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.90%29.aspx

Edit: Added the link

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

I mean this in a nice way.

That is a horrible naming convention that you have. Please change that mess. How in the world do you have any idea what is actually in any column?

Andrew SQLDBA

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

It's probably just a quick example, or an obfuscated table. See the column names that I use in my writing for useless.

Usually like

SELECT SomeInt, SomeString, SomeDate from SomeTable

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Hey Gila

Yes, I do the same, but I indicate such and surround the fake table name with tags to give a visual indicator.

ie....

Andrew SQLDBA

Krishna1 Hall of Fame Points: 3666 More actions · Answer 6

Tbale is having many columns which are not referd in the query. Hence i had used these column names which specify what datatype they have in the table.