CODEWITHSUNDEEP
sql-serverindexingsqlperformancenon-clustered-index
gkarya42
gkarya42

Reputation: 429

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.

Upvotes: 1

Views: 198

Answers (2)

StackUser
StackUser

Reputation: 5398

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 312379

The short answer is no.

If at least the first column in the index appears in your where clause, the index can be used. Whether or not it will be used is already up to the optimizer, but assuming you don't have any other indexes on other columns in the where clause, it will probably be used.

Upvotes: 1

Related Questions