andrew_dale (4/30/2015)
many thanks for the replies, they do make sense.checking the execution plan, if I run a simple query and include one of the index fields in the where clause then I get Clustered Index Seek if the where is on yps_ayrc and a Clustered Index Scan if the where is on yps_pslc
If I write a more complicated query, with a group by on yps_pslc, then it does a NonClustered Index Scan on ins_ypsI3
Don't forget to exclude from your query any columns which are not in the index. SELECT * FROM ... WHERE .. will almost always require reads from the clustered index. SELECT 1 FROM ... WHERE ... would be appropriate to use for this test, or better still, add only the columns which appear in the WHERE clause to the SELECT list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden