Note #1
According to ANSI SQL ’92 "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z". This is the reason why "WHERE h.OrderDate BETWEEN @StartDate AND @EndDate" is translated into "WHERE h.OrderDate >= @StartDate AND h.OrderDate <= @EndDate":
This means also that Y must be less or equal than Z (Y <= Z) and @StartDate must be less or equal than @EndDate (@StartDate <= @EndDate). Now, if we run following query having @StartDate = Dec 31, 2005 and @EndDate = Jan 1, 2005,
- SET STATISTICS IO ON;
- DECLARE @StartDate DATETIME = '20051231 23:59:59.997',
- @EndDate DATETIME = '20050101 00:00:00.000';
- SELECT COUNT(*) AS OrderCount1
- FROM Sales.SalesOrderHeader h
- WHERE h.OrderDate BETWEEN @StartDate AND @EndDate
- /*
- STATISTICS IO ON output:
- Table 'SalesOrderHeader'. Scan count 1, logical reads 686
- */
the result of COUNT(*) is 0 sales orders and the output of STATISTICS IO ON (the number of logical reads = the number of 8K data pages read from buffer pool) is 686 [logical reads]. This means that SQL Server reads data from Sales.SalesOrderHeader table though @StartDate <= @EndDate predicate is False for current values ("WHERE h.OrderDate BETWEEN ‘20051231 23:59:59.997′ AND ‘20050101 00:00:00.000’" or "WHERE h.OrderDate >= ‘20051231 23:59:59.997′ AND h.OrderDate <= ‘20050101 00:00:00.000’").
What options do we have in such cases ?
- We validate the values of parameters
- We tell SQL Server that Y must be less or equal than Z:
- SET STATISTICS IO ON;
- DECLARE @StartDate DATETIME = '20051231 23:59:59.997',
- @EndDate DATETIME = '20050101 00:00:00.000';
- SELECT COUNT(*) AS OrderCount1
- FROM Sales.SalesOrderHeader h
- WHERE h.OrderDate BETWEEN @StartDate AND @EndDate
- AND @StartDate <= @EndDate
- /*
- STATISTICS IO ON output:
- Table 'Worktable'. Scan count 0, logical reads 0
- */
This time, because SQL Server knows that @StartDate must be less or equal than @EndDate the output of STATISTICS IO ON shows that it never reads data from Sales.SalesOrderHeader table (0 logical reads). The execution plan of this query includes a Filter operator which checks @StartDate <= @EndDate predicate (‘20051231 23:59:59.997′ <= ‘20050101 00:00:00.000′) preventing (in this case) the execution of Index Scan / Index Seek operator and thus preventing reading data from Sales.SalesOrderHeader table:
Note #2
What if we want to count sales orders when @StartDate <= @EndDate but also when @StartDate > @EndDate thus avoiding the limitation of BETWEEN operator from ANSI SQL ? One solution is to rewrite the BETWEEN predicate in such way that BETWEEN always will bet BETWEEN @MinimumValue AND @MaximumValue.
Example:
- SET STATISTICS IO ON;
- DECLARE @StartDate DATETIME = '20051231 23:59:59.997',
- @EndDate DATETIME = '20050101 00:00:00.000';
- SELECT COUNT(*) AS OrderCount2
- FROM Sales.SalesOrderHeader h
- WHERE h.OrderDate
- BETWEEN
- – Minimum value
- (CASE WHEN @StartDate <= @EndDate THEN @StartDate ELSE @EndDate END)
- AND
- – Maximum value
- (CASE WHEN @StartDate <= @EndDate THEN @EndDate ELSE @StartDate END)
This solution has an advantage: is SARG-able and if there is an index on OrderDate column
- CREATE INDEX IX_SalesOrderHeader_OrderDate
- ON Sales.SalesOrderHeader (OrderDate);
then the execution plan will include an Index Seek operator:
