Non Clustered index scan/seek operator in SQL Server execution plan

The Non-Clustered Index Scan operator in SQL Server is used in execution plans when SQL Server scans an entire non-clustered index to retrieve data. A Non-Clustered Index Scan is similar to a Clustered Index Scan but only scans the non-clustered index, not the full table.

The Non-Clustered Index Scan appears in the execution plan when:

Ø  The query retrieves data from a non-clustered index because the required columns are part of or included in that index.

Ø  SQL Server determines that scanning the entire non-clustered index is more efficient than seeking specific rows.

Ø  A covering non-clustered index exists (i.e., the index includes all columns in the query), making it unnecessary to access the base table or clustered index.