Thanks, again, for your replies.

Yes, I was worried that I might be swapping one set of fast and slow plans for a new, different, set of fast and slow plans on the the basis of one which just happenend to be fast in SQL2005.

Tha main reason I converted to SQL2005 was to try to give you the more 'user friendly' set of plans, but clearly I can't do that.

Gail, I am still interested in what is going on here, specifically that the single change in an index from unique to nonunique can flip the query plan on its head.

I'm also interested in how you analyse the plan for this. I suspect there is not any way to 'nudge' the query plan back, but if you worked one out I would be most impressed. This is probably about as complex as it gets, so if it can be fixed, I'd guess just about anything could be. That would be a trick worth knowing.

But obviously, not urgent.

If you feel it's a better use of time, I could give you my skype ID and do a verbal rundown rather than try and type it all.

Re the recent comments (TheSQLGuru), I understand that this is not strictly a 'bug'.

The point is that the optimiser CAN derive a good plan, and even does derive a good plan for the tables and the views (ie. indexing is adequate, it's not a 'bad design' issue), but change this one index and it all goes down the gurgler for no apparent reason.

We can't expect that the optimiser will _always_ find a good plan, but when it doesn't it would be great to be able to feed it some hints to nudge it in the right direction.

But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?

I have tried the hints which would be logical here with no success.

Is there any way of giving the optimiser a bit more time to work out its plan ?

I realise the temp tables are a viable workaround (and one I have used in places), but this is for reporting and it means rather than simply constructing a query for a report I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end).

It raises the complexity of things greatly and given I have about 200 reports in the database, you can understand what this means in terms of time.

Now, if only I could get that pesky query plan to work ...

Ben