Key Lookup using columns outside of the index in SQL Query

Question

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

Execution Plan

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:

• 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?
• Why does the output list in Index1 include EndTime when that column is not present in Index1?
• How is Index2 being used to output ActivityId,AnotherId,PersonId when none of those columns are in Index2?

Apologies, I have pseudo-anonymised the plan and the query so I hope I have done it correctly!

Answer 1

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.