Mark Dalley (10/25/2016)
HanShi (10/25/2016)
You have defined the "TemplateName" column as the first in the composite key (clustered index), so the clustered index will be used if you use the "TemplateName" in the WHERE clause. An additional non-clustered index on "TemplateName" will not be used because it is a duplicate of the current clustered index.Ah, but the primary key is non-clustered!
Oh sorry, I overlooked. But it doesn't really matter unless the new non-clustered index will be a covering index for the query and the current primary key isn't.
To be sure you can look at the query plan before and after the creation of the index you want to add.
1. execute a (many used) query on the table and generate a query plan
2. create the additional non-clustered index
3. re-execute the query from step 1 and generate a query plan
4. compare the query plan from step 1 and 3 to see if the index usage has changed