FastSqlServer.com - Chris Dickey - SQL Server Performance Consultant
The important point is that an index seek will be used on the column that is being converted using the Date data type.
This option became available in SQL Server 2008 when the Date datatype was first introduced.
Using a function on a table column usually prevents an index seek. But not in this special case.
It is much easier to use this feature to solve SQL Server performance problems instead of doing a more complex query rewrite with an explicit date range comparison.
use AdventureWorks go /* add this index: CREATE NONCLUSTERED INDEX [ix_OrderDate] ON [Sales].[SalesOrderHeader]([OrderDate]) turn on "include actual execution plan" */
--this query does an index scan - a performance problem that needs to be fixed
select COUNT(*) from sales.SalesOrderHeader
where convert(varchar(10),OrderDate,120) = '2012-08-01'
--this simple change causes the query plan to use an index seek - problem solved
select COUNT(*) from sales.SalesOrderHeader
where CONVERT(Date,OrderDate) = '2012-08-01'
--this is an oftn recommended solution that is more complex
select COUNT(*) from sales.SalesOrderHeader
where OrderDate >= '2012-08-01' and OrderDate < CAST('2012-08-01' as datetime) + 1
Yes, Option(Recompile) on a query does force a compile. But it does much more!
If the query has table variables, the optimizer knows the exact number of rows instead of the estimate of 1 row.
If there are local variables, the optimizer knows the exact value so it has better selectivity estimates.
Since SQL Server 2008 SP2 and 2008 R1 SP1, the optimizer knows how to completely eliminate the branches in WHERE clauses that don't apply because of the value of a variable.
The following WHERE clause will have the 1st AND totally optimized out if @name IS NULL. Without Option(Recompile), the value in t.Name will have to be checked even if the value of @name used for the plan is NULL. This is a T-SQL pattern that is often used in report queries with optional parameters. That is one reason why option(recompile) is a big deal!
SELECT * from table t WHERE 1=1 AND (@name is NULL or @name = t.Name) AND (@id is NULL or @id = t.id) OPTION(Recompile)WITH RECOMPILE at the stored proc level only causes the queries in the proc to be recompiled. It does not have these other properties. The reason is that since Option(Recompile) is an attribute of the query, the optimizer knows that the query plan will only be used 1 time.