0

I have a WHERE clause with conditions on two columns:

WHERE A = 1 AND B = 2

Column A is a clustered index whereas column B is a non-clustered index.

My question is - are there any benefits in having a non-clustered index on column B in this specific query?

user11081980
  • 3,059
  • 4
  • 30
  • 48

1 Answers1

1

In that case 0 or one records is always returned because the cluster index, by default, is unique although it is not required. If the clustered index is unique it does not matter if column B has an index or not, it won't be used in this queries execution plan. Otherwise the index could be used depending on the statistics.

You can verify this by reading the execution plan.

Igor
  • 60,821
  • 10
  • 100
  • 175
  • 3
    Clustered indexes can be non-unique. A hidden “uniqifier” will be added for duplicate values of the index key columns. – David Browne - Microsoft Sep 08 '18 at 20:01
  • @DavidBrowne-Microsoft Will the non clustered index be even used in case the where clause is of the form : Where A > 1 and B < 100. Considering A is clustered and B is non clustered. – ashraydimri Mar 19 '21 at 07:31
  • 1
    @ashraydimri - That depends on the table and index statistics and then what query plan is chosen based on those statistics and the incoming statement and parameters. – Igor Mar 19 '21 at 09:53