>Here's a WAG first reaction on my part : I would be curious to know more about the HeadOffideCode colum - data type and contents. (and of course the exact indexes) For example, is there only AAA but might be BBB, BB1, BBB2.
>
>Would this make a difference?
>
>And what would be the difference if he used Where HeadofficeCode like 'BBB%'
>
>All of those are possible (and yes, data type definitely significant)
>
>I suspect there might not be any indexes (or any relevant ones) - the execution plan had table scans in it. Also, it looks like at least part of the view was using a linked server, and so the issue might be in something on another server (and I meant to ask him if the linked server was in SQL or Oracle and what that query might be doing)

Execution plan was weird when it started doing the scan. i was thinking the view may be deriving the HeadOfficeCode from parsing it from some other column. If the column is not derived in the view but is actually from a table and is char(3) then I guess differences on the index structures on the servers may account for it.

Agree that seeing the view code would be very helpful. (and of course to know the data type of that column )

(oh, by the way, I am eagerly following (and saving to folder) all the SQL tips you've started putting up here on UT. Hope that there is a way at some point to build them into an archive in the whitepaper section or someplace.)