I have a table like this:
CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [IntField4] [int] NOT NULL, [IntField5] [int] NOT NULL, [DateField1] [datetime] NOT NULL, [IntField6] [int] NOT NULL, [IntField7] [int] NOT NULL, [TextField1] [nvarchar](300) NULL, [DateField2] [datetime] NULL, [TextField2] [nvarchar](300) NULL, [DateField3] [datetime] NULL, [BoolField1] [bit] NULL )
I have created an index like this:
CREATE NONCLUSTERED INDEX IX_TestTable_DateField1 ON TestTable(DateField1);
And now I have this query:
DECLARE @startDate DATETIME = '20190101' , @endDate DATETIME = '20200101' SELECT [TestTableID], [IntField1], [IntField2], [IntField3], [IntField4], [IntField5], [DateField1], [IntField6], [IntField7], [TextField1], [DateField2], [TextField2], [DateField3], [BoolField1] FROM TestTable WHERE DateField1 >= @startDate AND DateField1 < @endDate
This table has nearly 10,000,000 records, and this query will return nearly 10,000 records.
Now, I expected the query to at least use my index IX_TestTable_DateField1 (Index Scan + Key Lookup) but it is doing a Clustered Index Scan (on PK field). I think this is because the query is returning all fields for the table.
My previous thinking was:
- If the index has INCLUDEd all fields, so the SqlServer will do an Index Seek;
- If not included all fields, but if the field is used in WHERE or ORDER, it will use Index Scan + Key Lookup;
- If neither 1 or 2, it will do a Clustered Index Scan;
Is this correct? Why an "Index Scan + Key Lookup" is not happening?