here is the execution plan in XML, seems to be doing a hash match, can you view this in ssms

query is

select [ElementID] ,

[mm] ,

[MPAN] ,

[Reference] ,

[yy]

from withClusteredIdx

<?xml version="1.0"...

Thanks for your reply, surely the performance of the query defining the index view shouldn't have an impact on a query referencing the view if the index covers all column...

Here is the script, is should return a years worth of data from @month and @year.

Surely the column order in the query is irrelevant to the optimizer?

create view withClusteredIdx

with...

select <field list req'd>

from <your table>

where <field with dupes> in (select <field with dupes> from <your table> group by <field with dupes>

having count(*) > 1)

I meant *no performance benefit

seems there was a lack of communication, my colleague had indeed created this role!