ebaya (11/1/2010)

---

jpenniman (11/1/2010)

---

Did you clear the buffers between tests? Using: dbcc dropcleanbuffers. If not, it would explain your "performance gain"

His illusory performance gain is explained entirely by the fact that it's an entirely differentquery, that will return different results. You don't need to invoke any buffering or anything else to explain it.

...as the result is already in memory. Your "faster" query would perform a table scan every time in real life, as your condition is an expression... and a case statement at that.

Eh? You can buffer an expression as you can anything else. You simply can't index off it (in technical terms, its not SARGable).