Jason-299789 (1/10/2013)
It is also my understanding that while you can get data being returned faster from the initial execute you can also end up with a sub optimal plan being used, is this correct?
Correct. The point of the hint is that you want to ensure that the first few rows arrive fast and you don't mind if then entire resultset takes longer than it would otherwise. The optimiser avoids blocking operations (like sorts or hashes) as far as possible, even if they may be the lowest cost option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability