I have a query as follows
SELECT ActivityId,
AnotherId,
PersonId,
StartTime AS MyAlias
FROM Activity
WHERE DeletedStatus='Active' AND
StartTime>='2018-02-01'AND StartTime<='2018-02-08'
The execution plan being used is here
Index1 is defined as:
CREATE NONCLUSTERED INDEX Index1 ON Activity
(
StartTime
)
Index 2 is defined as:
CREATE CLUSTERED INDEX Index2 ON Activity
(
EndTime
StartTime
)
The optimiser is using an index seek on Index1 and is then using a key lookup because ActivityId,AnotherId,PersonId, are in the SELECT
list but not in the index. This makes sense to me.
However, the following things puzzle me:
WHERE
clause?Apologies, I have pseudo-anonymised the plan and the query so I hope I have done it correctly!
Why is the optimiser able to use Index1 to do an index seek when DeletedStatus is not in the index but is in the WHERE clause?
The WHERE
clause also includes StartDate
so a seek can be performed using the provided StartDate
values followed by a range scan. The key lookup includes the 'Active' predicate to filter the rows per the WHERE
clause since that column is not included in the index.
Why does the output list in Index1 include EndTime when that column is not present in Index1?
All non-clustered indexes implicitly include the clustered index key as the row locator, similarly to explicitly included columns.
How is Index2 being used to output ActivityId,AnotherId,PersonId when none of those columns are in Index2?
The clustered index leaf nodes are the actual data rows so all columns are available.