Why is this datetime index not being used? 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 &gt;= @startDate
       AND DateField1 &lt; @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?
SQL server can definitely do an index seek and then a lookup, even when you don't cover the query.

The optimizer has no idea what values you have in your variables (that is how variables work). So it has to guess the selectivity. You can look at the actual execution plan and see how many rows it guesses. Apparently it guesses so many rows so it decides that it is better to do a table scan (cl ix scan).

If you add OPTION(RECOMPILE) at the end of the query, you should see a different estimated selectivity, and potentially usage of the index (all based on what selectivity you have in the end).

Also, if you have literals (values are known) or stored procedure parameters (values are sniffed), you'll see how it will estimate differently.