Order of the columns while creating an index

Question

Order of the columns while creating an index

vsamantha35

SSChampion

Points: 11529
More actions
December 6, 2017 at 10:53 am

#326739

Hi All,
Need a suggestion on Index creation. Suppose, If wanted to create a covering index on table "T2" how does it look like?
IF i had to take full advantage of the index, what should be the order of the columns while creating the index??
Please suggest.
Sample workload query:
========================
declare @userid int
set @userid = 90
SELECT
t1.c1,
t1.c2,
t2.address
FROM t1 samp
   JOIN t2
   ON t1.componentid = t2.componentid
    AND t2.userid = @userid
    AND t2.isreportassociation = 1
which one of the below ones will be beneficial for above query?
1. create index on t2(componentid) include(userid,isreportassociation,address);
2. create index on t2(componentid,userid,isreportassociation) include(address);
3. create index on t2(componentid,userid,isreportassociation,address)
4. or multiple single individual indexes on all the columns ?
Thanks,
Sam

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

vsamantha35 - Wednesday, December 6, 2017 10:53 AM
Hi All,
Need a suggestion on Index creation. Suppose, If wanted to create a covering index on table "T2" how does it look like?
IF i had to take full advantage of the index, what should be the order of the columns while creating the index??
Please suggest.
Sample workload query:
========================
declare @userid int
set @userid = 90
SELECT
t1.c1,
t1.c2,
t2.address
FROM t1 samp
   JOIN t2
   ON t1.componentid = t2.componentid
    AND t2.userid = @userid
    AND t2.isreportassociation = 1
which one of the below ones will be beneficial for above query?
1. create index on t2(componentid) include(userid,isreportassociation,address);
2. create index on t2(componentid,userid,isreportassociation) include(address);
3. create index on t2(componentid,userid,isreportassociation,address)
4. or multiple single individual indexes on all the columns ?
Thanks,
Sam

First, stay away from #4.
Without knowing the full schema on table t2, I would go with #2.

vsamantha35 SSChampion Points: 11529 More actions · Answer 2

Thanks Lynn. One follow up question. How does the schema of the tables make a difference in creating an index?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Determining whether I need a non-clustered index or a clustered index (there can be only one clustered index so choose wisely). You also need to look at how is the data being accessed.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Lynn Pettis - Wednesday, December 6, 2017 11:48 AM
Determining whether I need a non-clustered index or a clustered index (there can be only one clustered index so choose wisely). You also need to look at how is the data being accessed.

One more thing that could affect the index definition is the data distribution on each column.
Before implementing a covering index, we need to know if there's a clustered index in place, if it has different keys, if there are multiple bit flags, etc.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2