Pros/cons of two non-clustered indexes on Col1 and Col1+2

Question

In relation to a similar question, please consider the following:

• Microsoft SQL Server
• Table T has 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 B, C
• Non-clustered index #2 on B

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).

In particular:

• 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

Thanks.

Answer 1

In regards your main question: your DBA is probably not correct.

Note that the cost of maintaining the extra index may be significant, and in most cases will not result in any faster lookups.

The only benefit of an index over (B) when you already have (B, C) anyway is if C is very wide and you want to avoid having to read all that data when you don't need it. This would be only in a case where you were doing a big scan (or range seek) on B only.

But that would be an unusual case. In most cases it is sufficient to just have a single NC index on (B, C). For example, a point lookup of B on a single row will make little difference, and a scan or seek over both B and C cannot use this index. Equally if C is narrow then the gain even in the first case will be minimal.

This obviously assumes the two-column index is (B, C). If it's actually (C, B) then there could be a significant performance difference, as this index cannot be used to query B only.

---

On your other points:

• Clustered index defines the physical sequence in which the data is organized at the leaf nodes.
  So does a non-clustered index: its key defines the ordering of its leaf nodes.
• By design, the CI contains ALL columns.
  Correct, but only 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.
  Correct.
• Non-Clustered UNIQUE index contains the RID as an INCLUDED column,
  Partly correct: should read "contains the RID or clustering key"
  while non-clustered NON-UNIQUE index contains the clustered index as a key lookup column.
  Should read: "contains the the RID or clustering key as an index key column"
• Reads of the non-clustered index will reference the physical table data, when the NCI doesn't have all the columns needed by the query (eg: per select, where, etc).
  Correct mostly, that's called a Key Lookup, or RID Bookmark Lookup on a heap. In some cases an Index Union is used instead, to basically combine two separate NC indexes in a join rather than looking up a wide CI.