indexes that appear to be redundant with clustered PK

Question

I am working on a database at a client with the following table:

CREATE TABLE [Example] (
  [ID]         INT           IDENTITY (1, 1) NOT NULL,
  ....
  [AddressID]  INT NULL,
  [RepName]    VARCHAR(50) NULL,
  ....
  CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ([ID] ASC)
)

And it has the following indexes:

CREATE NONCLUSTERED INDEX [IDX_Example_Address]
  ON [example]( [ID] ASC, [AddressId] ASC);
CREATE NONCLUSTERED INDEX [IDX_Example_Rep]
  ON [example]( [ID] ASC, [RepName] ASC);

To me these are appear to be redundant with the clustered Index. I cannot imagine any scenario where these would be beneficial. If anyone can come up with a situation where these would be useful, let me know.

Here is another example:

CREATE NONCLUSTERED INDEX [IDX_Example_IsDeleted]
  ON [example]( [IsDeleted] ASC)
  INCLUDE( [ID], [SomeNumber]);

Why would you need to INCLUDE [ID]? My understanding is that the clustered index key is already present in every non-clustered index, so why would they do that? I would just INCLUDE ([SomeNumber])

I think I found my answer:http://stackoverflow.com/questions/2747308/is-it-bad-to-have-a-non-clustered-index-that-contains-the-primary-key-from-the-c — AndyBabs, Mar 21 '14 at 20:52

score 0 · Answer 1 · answered Mar 21 '14 at 21:05

You are correct in that the clustered index key is already included in every non-clustered index, but not in the same sense as your example clustered indices suggest.

For example, if you have a non-clustered index as in your example for IDX_Example_Rep, and you run this query:

SELECT [RepName], [Id] FROM [Example] WHERE [RepName] = 'some_value';

The IDX_Example_Rep index will be used, but it will be an index scan (every row will be checked). This is because the [Id] column was specified as the first column in the index.

If the index is instead specified as follows:

CREATE NONCLUSTERED INDEX [IDX_Example_Rep] ON [example]([RepName] ASC);

Then when you run the same sample query, the IDX_Example_Rep index is used and the operation is an index seek - the engine knows exactly where to find the records by [RepName] within the IDX_Example_Rep index and, because the only other field being returned by the SELECT is the [Id] field, which is the key of the clustered index and therefore included in the non-clustered index, no further operations are necessary.

If the SELECT list were expanded to include, say, the [AddressId] field, then you'll find the engine still performs the index seek against IDX_Example_Rep to find the correct records, but then also has do a key lookup against the clustered index to get the "other" fields (the [AddressId] in this example).

So, no - you probably don't want to repeat the [Id] column as part of the non-clustered indices in general, but when it comes to non-clustered indices you definitely want to pay attention to your SELECTed fields and know whether or not you're covering the fields you're going to need.

indexes that appear to be redundant with clustered PK

1 Answers1