In relation to a similar question, please consider the following:
- Microsoft SQL Server
Thas at least 4 columns
A, B, C and D
- PK is on
A(i.e. non-null, unique, and the clustered index)
- Non-clustered composite index #1 on
- Non-clustered index #2 on
I was informed by the DBA that both indexes were needed, after I politely argued that index #2 was redundant / could be deleted, since index #1 was sufficient for accessing the table by (B) or (B+C).
IIRC, the DBA mentioned paging being his reasoning to retain both.
Question #1: is the DBA correct?
Question #2: what are the pros/cons of having the 2 NC indexes, regardless of wanting to retain both?
I do understand the major differences of clustered (CI) / non-clustered (NCI).
- A CI, at the Leaf Nodes, is the actual table data, stored in the defined sequence.
- Hence, by design, the CI contains ALL columns at the Leaf level.
- In the NCI, the Leaf Nodes contain Pointers* to the corresponding clustered index key. If no clustered index exists, then they contain RID (row identifiers) of the physical HEAP table. *Pointer defined as a copy of the clustered index key column(s) not already used as non-clustered index key column(s).
- Non-Clustered UNIQUE index contains the RID or Clustered Index Key as an INCLUDED column, while non-clustered NON-UNIQUE index contains the RID or Clustered Index Key as an Index Key column.
- Reads of the non-clustered index will reference the physical table data (aka "Key Lookup" or, on a heap, "RID Bookmark Lookup"), when the NCI doesn't have all the columns needed by the query (eg: per select, where, etc). Note that, in some cases, an Index Union is leveraged instead, combining two separate NC Indexes in a join, rather than looking up a Wide CI.
Per Gail Shaw:
Composite nonclustered indexes are generally more useful than single column indexes, unless all queries against the table filter on one column at a time. ~ https://www.sqlservercentral.com/articles/introduction-to-indexes