1

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates.

So, for example, if we have a table with columns A, B, C, etc we may have a query that filters on columns A and B but not C, a second query that filters on A and C but not B, and so on.

Does it make sense to have a non-clustered index that has as its key columns every column in the table (or tables) that can be a predicate?

To my mind, I'd have thought it is better to have a non-clustered index for each query with the predicates for that query as the key columns.

Any advice on this would be great!

  • Generally, no, you won't be able to create a single index that optimally supports all of those predicate combinations. Are all three columns similarly selective? Are they all the same data type? Are all the filters equality or are some based on ranges or patterns? – Aaron Bertrand Mar 24 '15 at 11:05
  • Hi Aaron, they are of different data types. – David Brower Mar 24 '15 at 12:21
1

Multiple aspects needs to be considered when creating an index. An index on columns A,B is not the same as an index on B,A. the performance of a query which filters on both column A and B will be different depending on the order of the columns in the index.

Indexes slow down DML operations and increase the size of the database.

You can refer to the stackoverflow link below for more details. https://stackoverflow.com/questions/5447987/why-cant-i-simply-add-an-index-that-includes-all-columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.