0

I have a query which has an optional where condition on column classID(not unique). This same classID is being displayed as a select column in various other queries. Currently i have classID as an included column in an index idx,so i think that the idx will be used when classID is in select statement. Do i have to create a a non clustered index for the classID as a key column when the query has where condition for classID.?

Confused on this whether to create separate non-clustered indexes for all the where clause columns.?

  • 2
    So your question is can your current index (which would be helpful to include in the question) which only has ClassID as an included column, could be used for the predicate in the WHERE clause, correct? If so check Shawn's answer. For an index to be used for your predicates, they need to be a key column. – scsimon Oct 14 at 19:16
  • Thanks that was perfect for my question.So if i have classID as key column ,then that will help when i have a where clause of classID.If i have classID in the select and if i don't have classID as an included column ,but i have classID as a keycolumn,would that work as well.Or should i have classID as included column as well. – user9516827 Oct 14 at 19:59
  • 2
    If ClassID is a key column, then it's not necessary for it to be an included column. That would be redundant (and might even throw an error). Is ClassID the only predicate in the Where clause? If not, consider the full set of predicates. Queries would benefit if there is an index that matches the predicates in the same order as they are listed in the Where clause. But as always you want to balance that benefit versus the cost of maintaining the index. – Conrad S. Oct 14 at 20:23
3

Currently i have classID as an included column in an index idx, so i think that the idx will be used when classID is in select statement.

No, not really. The index that is selected in the query plan depends on the query optimiser deciding which index best satisfies the query requirements. If your index key columns match, or are very similar to, your query predicates then it will likely select that index.

Having ClassID in the INCLUDE columns simply means the query plan will not require key lookups to retrieve this additional data about the rows returned in the query. If ClassID is a predicate, having it in the INCLUDE columns of an index will not cause the optimiser to definitely select that index.

Do i have to create a a non clustered index for the classID as a key column when the query has where condition for classID.?

You need to create indexes that best satisfy the most common data access. For example, if you create a bunch of indexes with single key columns and many include columns, but your queries have many predicates and few select columns, then your indexing strategy is unlikely to be as efficient as it should be.

A good starting point/rule of thumb while you're getting started with index tuning\design is no more than 5 indexes per table, and no more than 5 columns per index. This helps you to be more selective about what indexes you create and how you design them to satisfy the most queries.

It is not a hard and fast rule, it just helps you to think about your index strategy relative to your queries.

Confused on this whether to create separate non-clustered indexes for all the where clause columns.?

You don't create separate indexes for each predicate because the optimiser will likely only use one of them and then do a bunch of key lookups. Design your indexes with this basic guideline:

  • WHERE clause and JOIN columns in your query should be Key Columns in your Index.
  • SELECT columns in your query should be Include columns in your index.

These aren't hard and fast rules, and again don't design your indexes to satisfy a single query. You need to profile your data access characteristics and develop an indexing strategy to satisfy the most queries with the fewest indexes for maximum efficiency.

Some good resources:

SQL Server Index Basics

SQL Server Index Architecture and Design Guide

SQL Server Index Basic Design and Guidelines

Designing Effective Non-Clustered Indexes

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.