Andre Ranieri (12/17/2012)
if the left side of the equal sign is a calculation, then the WHERE clause is "non sargeable", meaning that the optimizer can't use simple search arguments and must instead build an efficient query plan and must calculate every field in your table in this matter.Maybe something like
-- midnight today
DECLARE @dFrom datetime = dateadd(dd, datediff(dd, 0, getdate()), 0)
-- 23:59:59 today
DECLARE @dTo datetime = DATEADD(dd, 1, @dFrom)
Then, in your WHERE clause:
[datetime] >= @dFrom AND [datetime] < @dTo
See if you get a better execution plan with this.
There is a very good chance that this will cause a table scan. A query plan is created only for data access statements (e.g. select, insert, update, delete, etc'). It completely ignores a variables assignment which is done in memory. In your example, the server will create a query plan without knowing the values of @dFrom and @dTo, so it will guess that 20% of the table's records will be selected. In the vast majority of times this will cause a table scan. It can work if you'll have the select statement in a different procedure and you'll send the values of @dFrom and @dTo as parameters to the procedure. This is because in the case of procedures and parameters, the server works with parameter sniffing.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/