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

Question

Viewing the Execution plan, i see "column A" in the Output List. The operation is an Index Scan on a Non-Clustered Index : "IX_name"

When i see the definition of this index. I do not see "column A" in either Index Key columns or Included columns.

How is a Non-Clustered index being used to output a column that is not present in the index. Shouldn't it use a Table Scan on the table or some other index which has "column A" present in it.

@Tomalak, due to some restrictions i can not do that. Really sorry for that. I can provide all the information you might need. — blogbydev, Apr 14 '14 at 09:49
Specifically I wanted to know if a bookmark lookup operation appears in the plan. — Tomalak, Apr 14 '14 at 10:37

Branko Dimitrijevic · Accepted Answer · 2014-04-14T11:55:59.410

If the table itself is clustered¹, then all secondary indexes contain a copy of the clustering key² (a key that determines the physical order of rows in the clustered table).

The reason: rows in a clustered table are physically stored within a B-tree (not table heap), and therefore can move when B-tree nodes get split or coalesced, so the secondary index cannot just contain the row "pointer" (since it would be in danger of "dangling" after the row moves).

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

First, search the secondary index and get the clustering key.
Second, based on the clustering key retrieved above, search the clustered table itself (which is B-tree).

However, if all you want are the fields of the clustering key, only the first lookup is needed.

¹ Aka "clustered index" under MS SQL Server.

² Usually, but not necessarily a PRIMARY KEY under MS SQL Server.

³ It is unfortunate that clustering is on by default under MS SQL Server - people often just leave the default without fully considering its effects. When clustering is not appropriate, you should specify NONCLUSTERED keyword explicitly to turn it off.

Will this mean that adding the clustering key explicitly in the "Included Columns" is never required(in case we need the clustering key column)? — blogbydev, Apr 14 '14 at 10:07

score 1 · Answer 2 · answered Apr 14 '14 at 09:45

1

Internally, a non-clustered index contains all key columns of the clustered key. This is to support the key lookup operation and to ensure that internally each index row has a unique key. For heaps each NCI contains the row bookmark for the same reasons.

answered Apr 14 '14 at 09:45

usr

168,620
35
240
369

Thanks @usr for your response. Can you direct me to some article or tutorial for this where i can get a better understanding. This will really be helpful. – blogbydev Apr 14 '14 at 09:51
For example http://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx. Search for "SQL Server index internals". – usr Apr 14 '14 at 10:18

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

2 Answers2

Linked