Recently I was at the SQL Saturday in Oslo. In one of the lessons (with Joe Chang) I was told that “between” was faster than using “month”, “year” and similar functions when comparing dates. They use the index in a better way.

I did a quick test this evening. And here is my findings.

--Create and index
CREATE NONCLUSTERED INDEX IX_FactInternetSales_OrderDate 
ON dbo.FactInternetSales
(
	OrderDate
) 
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- Turn on statistics
SET STATISTICS IO ON

-- Query 1
select * from FactInternetSales
where year(OrderDate) = 2005 and month(OrderDate) = 07

-- Query 2
select * from FactInternetSales
where OrderDate between '2005/07/01' and '2005/07/31'

The IO statistics looked like this

(146 row(s) affected)
Table 'FactInternetSales'. Scan count 1, logical reads 689, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(146 row(s) affected)
Table 'FactInternetSales'. Scan count 1, logical reads 459, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

As you can see much less logical read on query 2.

And the actual execution plan looked like this

The conclusion is that mr Chang knows what he is talking about 🙂