Tao Klerks (4/28/2009)
Are you saying that the following statement would result in a better query plan? (or more reliably result in a good query plan?)
SELECT *FROM SomeTable
WHERE (SomeColumn >= '2009-04-01' AND SomeColumn < '2009-04-16' )
Absolutely yes. Though you might need to add an OPTION (RECOMPILE) to prevent re-use of any previous plan, especially if forced parameterization is in effect.
Tao Klerks (4/28/2009)
If so, why would that be? Because in one case SQL Server can read the values and choose an appropriate query plan based on statistics / known value distributions, and in the other case it cannot?
Yes! See http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
Tao Klerks (4/28/2009)
In that case, does adding a function call inline break it?
Why don't you test it and tell everyone what you find? 😉
Tao Klerks (4/28/2009)
Is there any reference to this issue / mechanism anywhere online that I can look at?
The link above is to a blog entry from the SQL Server QUery Optimization Team.
Google 'SQL Server 2005 parameter sniffing' and you will turn up many good articles, including some on this site.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi