Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Columns to be included in Index Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 11:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1436264
Posted Thursday, March 28, 2013 3:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 13,614, Visits: 10,497
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1436356
Posted Thursday, March 28, 2013 4:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1436365
Posted Thursday, March 28, 2013 8:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
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
Post #1436493
Posted Thursday, March 28, 2013 8:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 42,814, Visits: 35,937
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 2008, MVP
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

Post #1436523
Posted Thursday, March 28, 2013 9:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
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....

<TableName>.<ColumnName>

Andrew SQLDBA
Post #1436542
Posted Sunday, March 31, 2013 12:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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.
Post #1437272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse