Exactly right. Even the optimizer will sometimes do Scans (table or Index scan) on smaller things even when an index is available because it determines that a Scan would actually faster.

Here's a test of the code with substantially more. I ran this on my 4 core laptop. I agree that the difference in duration is not "substantial" in this case and that's because duration includes returns to the screen which probably would not be done in most cases. Returning data to the screen is sometimes called the "Great Equalizer" because no matter how fast your code actually is, it takes roughly the same amount of time to display the same amount of data no matter what. If you were to drive the output to variables to take the display out of the picture, you'd see a whole 'nuther story.

Note that I did add an "=" sign to your code to pick up when "today" was the end date.

Also notice that I do 4 runs each. The first run is right after the DBCC stuff and simulates what would happen if, indeed, things weren't already cached. In real life, though, it's likely that all of this would be in cache.

Because scan for lower numbers of rows are almost as fast or sometimes faster than using indexes, people are frequently fooled that their code is good enough without realizing that execution plans do change when scale of the problem changes. I also never justify non-SARGable code even when low numbers of rows are supposedly "guaranteed" because 1) that can change in a heartbeat and 2) someone else my pick up my code for use on something much larger especially if they're in a hurry for a solution.

--=============================================================================

-- Create a larger test table with the appropriate index

-- Only adding the appropriate index is a part of the solution

--=============================================================================

--DROP TABLE dbo.#DateTest

GO

--===== Create the table, as before

CREATE TABLE dbo.#DateTest

(

FromDate varchar(9)

,ToDate varchar(9)

)

;

WITH

cteFromDate AS

(

SELECT TOP 5000000

FromDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2011','2015'),'2011')

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

)

INSERT INTO #DateTest

(FromDate,ToDate)

SELECT FromDate = CONVERT(CHAR(8),fd.FromDate,112)

+ CONVERT(CHAR(1),ABS(CHECKSUM(NEWID()))%2+1)

,ToDate = CONVERT(CHAR(8),DATEADD(dd,ABS(CHECKSUM(NEWID()))%90,fd.FromDate),112)

+ CONVERT(CHAR(1),ABS(CHECKSUM(NEWID()))%2+1)

FROM cteFromDate fd

;

--===== Add the expected index

CREATE CLUSTERED INDEX IX_#DateTest

ON #DateTest (FromDate,ToDate)

;

--=============================================================================

-- Demonstrate that the current solution will NOT do an Index Seek

-- and a method that will. The Index Seek is followed by a nice

-- high performance range scan

--=============================================================================

GO

PRINT '========== Lempster =================================================='

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

--===== Setup the variable for @Today to make testing easy

DECLARE @Today INT;

SELECT @Today = CAST(CONVERT(varchar,getdate(),112) AS int) --Or whatever

;

--===== This CANNOT do an Index Seek because of the formulas

-- on the FromDate and ToDate columns (non-SARGable).

SET STATISTICS TIME,IO ON;

SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate

,ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate

FROM dbo.#DateTest

WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today

AND ISNULL(SUBSTRING(ToDate,1,8),99991231) >= @Today;

SET STATISTICS TIME,IO OFF;

GO 4

PRINT '========== SARGable =================================================='

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

GO

--===== Setup the variable for @Today to make testing easy

DECLARE @Today DATETIME;

SELECT @Today = GETDATE() --Or whatever

;

--===== This DOES do an Index Seek because there are no forumulas

-- on the table columns in the WHERE clause (SARGable).

SET STATISTICS TIME,IO ON;

SELECT FromDate = ISNULL(LEFT(FromDate,8),'19000101')

,ToDate = ISNULL(LEFT(ToDate ,8),'99991231')

FROM dbo.#DateTest

WHERE (FromDate <= CONVERT(CHAR(8),@Today ,112)+'9' OR FromDate IS NULL)

AND (ToDate >= CONVERT(CHAR(8),@Today ,112)+'0' OR ToDate IS NULL);

SET STATISTICS TIME,IO OFF;

GO 4

Here are the run results.

========== Lempster ==================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Beginning execution loop

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 3, logical reads 24029, physical reads 241, read-ahead reads 23902, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2200 ms, elapsed time = 2353 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 3, logical reads 24029, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2043 ms, elapsed time = 1734 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 3, logical reads 24029, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2013 ms, elapsed time = 1677 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 3, logical reads 24029, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2059 ms, elapsed time = 1731 ms.

Batch execution completed 4 times.

========== SARGable ==================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Beginning execution loop

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 2, logical reads 18529, physical reads 279, read-ahead reads 18427, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 858 ms, elapsed time = 3594 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 2, logical reads 18529, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 687 ms, elapsed time = 1614 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 2, logical reads 18529, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 639 ms, elapsed time = 1569 ms.

(156116 row(s) affected)

Table '#DateTest___________________________________________________________________________________________________________000000000088'.

Scan count 2, logical reads 18529, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 1596 ms.

Batch execution completed 4 times.

The truth be told, they're both not that great because of the mixed character data in denormalized columns instead of using the correct DATETIME datatype.

Still, the SARGable code is almost 3 times faster overall. Imagine if all the code on your database were a mere 3 times faster. With the possible exception of SSDs (which I don't believe would help here), you can't buy a machine that's 3 times faster.