Optional parameters and date index
I'm playing around with two very simple queries. There is a non-clustered index with StartDate
and EndDate
, as well as Id
as an included column.
DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '4/1/2011', @endDate = '5/1/2011'
-- Does Index Scan (slow)
SELECT Id
FROM dbo.Table
WHERE
(@startDate IS NULL OR StartDate >= @startDate) AND
(@endDate IS NULL OR EndDate < @endDate)
-- Does Index Seek (fast)
SELECT Id
FROM dbo.Table
WHERE
(StartDate >= @startDate) AND
(EndDate < @endDate)
Is there any way to rearrange, pre-calculate, or otherwise change the query to have an index seek occur in the first example?
Edit: I know this is a very basic indexing problem, but I haven't found a good solution yet. Note that I am declaring the variables, b开发者_如何学Pythonut those would be parameters in an sproc.
What about the following?
DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '4/1/2011', @endDate = '5/1/2011'
SELECT Id
FROM dbo.Table
WHERE
StartDate >= ISNULL(@startDate, '1/1/1753')
AND
EndDate < ISNULL(@endDate, '12/31/9999')
This code is probably broken if you have an end date of 12/31/9999 in your table that you actually want returned from your result set, but how often does that happen?
精彩评论