0

I have an index on two columns of pretty big table (100+ million rows) in MS SQL. I need an index that covers those two columns plus a third one to improve the performance of a different query.

Should I modify the existing index to include all three columns or create a completely new one?

Nicola Ambrosetti
  • 2,567
  • 3
  • 22
  • 38

1 Answers1

0

Should I modify the existing index to include all three columns or create a completely new one?

It depends on the way you query the table.

Assume you have below index..

create index nci on tablea(Col1,col2)

below queries will satsify the index

select * from table1 where col1=1 and col2>10
select * from table1 where col1=1 and col2=10

below query wont satisfy above index

select col1,col2 from table1 where col1>40 and col2=40

So second column is not useful ,if you use any comparison other than equality on first column.The same applies if you add third column

In summary, you can add third column ,if you need your queries to be covering and also they fall into above sample category(equality on first column)..

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 1
    >>>select col1,col2 from table1 where col1>40 and col2=40 So second column is not useful ,if you use any comparison other than equality on first column.<<< That is not true. This query returns only col1, col2. The original table can have 100 columns. So the index on (Col1,col2) will be used (index scan) as it is covering and the narrowest – sepupic Oct 13 '17 at 10:20
  • I assume "tablea" in the create index statement and table1 in the queries are the same table and it's a typo? – SE1986 Oct 13 '17 at 13:38
  • i meant to say inequality on first column leads to residual predicates @sepupic – TheGameiswar Oct 13 '17 at 16:38