IF object_id('DateRange') IS NOT NULL
    DROP TABLE DateRange
GO
CREATE TABLE DateRange (id INT IDENTITY, dt DATE);
CREATE INDEX IX_DateRange_Dt ON DateRange(dt);

; WITH  list AS
        (
        SELECT  CAST('2000-01-01' AS DATE) AS dt
        UNION ALL
        SELECT  dateadd(DAY, 1, dt)
        FROM    list
        WHERE   dt < '2009-12-31'
        )
INSERT  dbo.DateRange
        (dt)
SELECT  dt
FROM    list
OPTION  (maxrecursion 0);

-- Uses index seek
SELECT  *
FROM    DateRange
WHERE   dt BETWEEN '2005-01-01' AND '2005-01-05'
OPTION  (recompile)

-- Uses table scan
SELECT  *
FROM    DateRange
WHERE   dt BETWEEN '2005-01-01' AND '2005-01-06'
OPTION  (recompile)