Database Reference
In-Depth Information
Date Part Comparison
SQL Server can store date and time data as separate fields or as a combined
DATETIME
field that has both. Although
you may need to keep date and time data together in one field, sometimes you want only the date, which usually
means you have to apply a conversion function to extract the date part from the
DATETIME
data type. Doing this
prevents the optimizer from choosing the index on the column, as shown in the following example.
First, there needs to be a good index on the
DATETIME
column of one of the tables. Use
Sales.SalesOrderHeader
and create the following index:
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]')
AND name = N'IndexTest' )
DROP INDEX IndexTest ON [Sales].[SalesOrderHeader];
GO
CREATE INDEX IndexTest ON Sales.SalesOrderHeader(OrderDate);
To retrieve all rows from
Sales.SalesOrderHeader
with
OrderDate
in the month of April in the year 2008, you can
execute the following
SELECT
statement:
SELECT soh.SalesOrderID,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE DATEPART(yy, soh.OrderDate) = 2008
AND DATEPART(mm, soh.OrderDate) = 4;
Using the
DATEPART
function on the column
OrderDate
prevents the optimizer from properly using the index
IndexTest
on the column and instead causes a scan, as shown in Figure
18-10
.
Figure 18-10.
Execution plan showing the detrimental effect of using the
DATEPART
function on a
WHERE
clause column
This is the output of
SET STATISTICS IO
and
TIME
:
Table 'Worktable'. Scan count 0, logical reads 0 Table 'SalesOrderDetail'. Scan count 1, logical
reads 276 Table 'SalesOrderHeader'. Scan count 1, logical reads 73
CPU time = 15 ms, elapsed time = 143 ms.
