Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and which columns should i use in incluse list. I am using SQL Server 2008/R2
In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:
So we have a test table, SomeTable, with a clustered index. Execute the below query:
This causes a Clustered Index Scan, like you are seeing:
So analyzing the query, we have the column AnotherInt that is in the
Now, executing the same query above:
SQL Server will utilize that non-clustered index to return the data. It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:
Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.