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?