2

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.

0

1 Answer 1

2

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.
5
  • Thanks for the clarifications! Per 1st bullet response, I was inferring a key difference of the CI being the Leaf Level is the actual table data. Per 2nd bullet, that was inferred but did benefit from clarification. My point was simply that CI is therefore faster at retrieving data and is unlike an NCI which can optionally have additional columns INCLUDED. Per 4th bullet, was a literal quote from Gail Shaw's post here.
    – Paulie D
    Commented Aug 5, 2023 at 0:47
  • 1
    @PaulieD 4th point I can't see anywhere Gail mentions your wording. Both unique and non-unique would have either RID or clustering key, dependent whether the table is clustered or not. Commented Aug 6, 2023 at 1:29
  • @PaulWhite True, but if C is narrow then the extra cost of scanning that data at the same time isn't much and probably not worth maintaining an extra index. Let's not go into why paging by row-number is a bad idea anyway and if that's what you're doing you have bigger fish to fry. Commented Aug 6, 2023 at 1:31
  • 1
    " Leaf Nodes contain pointers to the corresponding clustered index key" in particular the "pointer" is a copy of the clustered index key columns not already used as non-clustered index key columns. Commented Aug 8, 2023 at 0:45
  • @Charlieface Thanks! I've incorporated your valued feedback, into my OP.
    – Paulie D
    Commented Oct 19, 2023 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.