0

I'm looking to optimize a clustered index based on my where and order by clause.

where Active = 1 and Deleted = 0
order by ControlName

In my clustered index the order of my columns is Active, Deleted, and ControlName.

Can I optimize this further?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Mike Malter
  • 1,018
  • 1
  • 14
  • 38
  • Does this answer your question? [How to choose the clustered index in SQL Server?](https://stackoverflow.com/questions/2267326/how-to-choose-the-clustered-index-in-sql-server) – SMor Jul 31 '20 at 18:00
  • You optimize queries by introducing the appropriate indexes. You have only ONE clustered index and that choice must serve multiple purposes. If i had to guess, a filtered index (or indexed view) might be more useful since you seem to be looking for "current" rows and such queries are far more common in an active OLTP system. – SMor Jul 31 '20 at 18:04
  • Thanks for the answer, but it doesn't address the point I raised in my question. I was looking to optimize a clustered index based on the contents of my where clause and order by. – Mike Malter Jul 31 '20 at 22:22

1 Answers1

1

For this query, the index you want is either (Active, Deleted, ControlName) or (Deleted, Active, ControlName).

It does not have to be a clustered index.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the answer. I was thinking that, but not sure. And I did try it with a non-clustered index and the optimizer chose the right index. – Mike Malter Jul 31 '20 at 22:21