I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total). One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that table within a certain date range. Seems like a good candidate for a filtered index to me.
This is my filtered index:
CREATE NONCLUSTERED INDEX IX_Filtered_Table1_DateField1
ON Table1 (DateField1, PrimaryKeyField)
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')
This is my query:
DECLARE @MinPrimaryKeyId BIGINT = 2854868995
SELECT TOP 500000 PrimaryKeyField
INTO #Results
FROM Table1 WITH(INDEX(IX_Filtered_Table1_DateField1))
WHERE DateField1 >= '2/24/20'
and DateField1 < '4/14/20'
and PrimaryKeyField > @MinPrimaryKeyId
ORDER BY PrimaryKeyField ASC
When checking out the execution plan, I notice it's using my filtered index but it's defaulting to an Index Scan operation instead of an Index Seek.
If I try try to use the FORCESEEK query hint (in addition to my index hint), I get the classic error:
Query processor could not produce a query plan because of the hints defined in this query
Why would the query processor be unable to generate an execution plan using a Seek operation on this index when it can do a Scan operation with it? (If I switch out my FORCESEEK query hint with a FORCESCAN it works, which I know is no different than not using a hint at all here.)