Search code examples
sql-serverindexingsqlperformancenon-clustered-index

Using non clustered index for efficient searching

I have created non clustered index on combination of three columns. Do I need to specify all the three columns in where condition to used non clustered index.

Solution

  • In order to utilize your index you have to use like this.

    Index: Ix_threecols(c1,c2,c3)

    1. select c1,c2,c3 from table where c1=val
and c2=val and c3=val
2. select c1,c2,c3 from table where c1=val
and c2=val
3. select c1,c2,c3 from table where c1=val

    Your left most first column in the index should be used in the where clause. Please don't use (*) in your select since it may lead to index scan.

    We have to check the execution plan for more analysis on this.