2

For example, here are 2 indexes on FOO table:

ALTER TABLE [dbo].[FOO] ADD CONSTRAINT [PK_FOO] PRIMARY KEY CLUSTERED ([id] ASC) CREATE NONCLUSTERED INDEX [IX_FOO] ON [dbo].[FOO] ([id] ASC, [a] ASC, [b] ASC)

Many queries are using IX_FOO when filtering with a column. And it seems id column in IX_FOO is redundant since PK_FOO is indexing it. So I'm thinking to remove id column from IX_FOO like this:

CREATE NONCLUSTERED INDEX [IX_FOO2] ON [dbo].[FOO] ([a] ASC, [b] ASC) INCLUDE ([id])

But I'm not sure myself. Do indexes need to include PK columns?

Improve this question
3
  • It's not clear what you're asking. Commented Jun 15, 2018 at 2:56
  • @ryanwebjackson I'm thinking to remove id column from IX_FOO since PK_FOO already indexing it. But I'm not sure if this is a rational idea. Commented Jun 15, 2018 at 2:59
  • Makes sense to me, but I'm not a SQL expert. Commented Jun 15, 2018 at 3:01

2 Answers 2

Reset to default
2

The order of columns in index is important.

Index on (ID, a, b) is very different to index on (a, b) include (id).

The first index on (ID, a, b) can be used when searching by:

  1. ID, a, b: WHERE ID = 1 AND a = 2 AND b = 3
  2. ID, a, range of b: WHERE ID = 1 AND a = 2 AND b > 3 AND b < 5
  3. ID, a: WHERE ID = 1 AND a = 2
  4. ID, range of a: WHERE ID = 1 AND a > 2 AND a < 5
  5. ID: WHERE ID = 1
  6. range of ID: WHERE ID > 1 AND ID < 5

This index will not be used when searching just for a or just for b: WHERE a = 2; WHERE b = 3.

Selected columns can be any combination of ID, a, b - if there are more columns, index would be not enough and engine would have to read them from the table.

The second index on (a, b) include (id) can be used when searching by:

  1. a, b: WHERE a = 2 AND b = 3
  2. a, range of b: WHERE a = 2 AND b > 3 AND b < 5
  3. a: WHERE a = 2
  4. range of a: WHERE a > 2 AND a < 5

This index will not be used when searching just for ID or just for b: WHERE ID = 1; WHERE b = 3.

Selected columns also can be any combination of ID, a, b - if there are more columns, index would be not enough and engine would have to read them from the table.

If there is a clustered index on ID, then there is no point adding ID as INCLUDE to non-clustered index, because the clustered column is included in each non-clustered index implicitly. This is why it is generally recommended to have narrow clustered index, usually 4-byte int. The wider the clustered index, the more space is needed for each non-clustered index.

So, index on (a, b) include (id) is the same as (a, b) in your case.

I'm not sure if engine is smart enough to not waste disk space if you INCLUDE ID explicitly. It is easy to check.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Question about WHERE ID = 1 AND a > 2 AND a < 5: Is index like IX_FOO necessary for this search? I thought PK_FOO would be sufficient since id is unique and checking a range of a is not expensive. Maybe there is a caveat?
@user2652379, You are right, I wasn't thinking about PK_FOO, I was focusing on the difference between two variants on the non-clustered index. With ID being unique it doesn't matter which index to use PK_FOO or IX_FOO. Taking into account uniqueness of ID it doesn't make sense to have IX_FOO defined as it is. It is pretty much useless. All searches listed above that can be done efficiently with IX_FOO can be done with the same efficiency using PK_FOO when ID is unique.
2

No you don't. Non Clustered indexes will be pointing to the primary key of the main table hence there is no need to add it (Primary Key) to any non clustered index.

In other words all non clustered indexes implicitly includes the clustered index of the table.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.