sql - How can a Non-Clustered index output a column that is not included in the index -

viewing execution plan, see "column a" in output list. operation index scan on non-clustered index : "ix_name"

when see definition of index. not see "column a" in either index key columns or included columns.

how non-clustered index being used output column not present in index. shouldn't use table scan on table or other index has "column a" present in it.

if table clustered¹, secondary indexes contain copy of clustering key² (a key determines physical order of rows in clustered table).

the reason: rows in clustered table physically stored within b-tree (not table heap), , therefore can move when b-tree nodes split or coalesced, secondary index cannot contain row "pointer" (since in danger of "dangling" after row moves).

often, has detrimental effect on performance³ - querying through secondary index may require double-lookup:

• first, search secondary index , clustering key.
• second, based on clustering key retrieved above, search clustered table (which b-tree).

however, if want fields of clustering key, first lookup needed.

---

¹ aka "clustered index" under ms sql server.

² usually, not primary key under ms sql server.

³ unfortunate clustering on default under ms sql server - people leave default without considering effects. when clustering not appropriate, should specify nonclustered keyword explicitly turn off.