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