1

I have a table like :

create table x (a varchar(20),
b varchar(30),
c varchar(30),
primary key clustered(a asc))

so I'm wondering if I create an index like:

Create nonclustered index test on x(a) include (b,c)

that index will make my querys that use columns a,b,c faster?

1 Answer 1

9

In this case, the non-clustered index is essentially a duplicate of the primary key*. It will take up extra space and make inserts/updates/deletes slower, but it will not add any benefit.

The reason is that any query that can be answered by seeking on column a can perform that seek on the primary key and has no need for the non-clustered index.

However, if your actual table has many additional columns beyond a, b, and c (e.g., let's say there are 100 other columns as well), it's possible that the non-clustered index could improve queries that use only a, b, and c. In that case, it would provide a smaller data structure that can be used to access only these three columns. It would depend on your workload whether this is worth the cost of maintaining the index.


* The primary key is the clustered index here and, as Max Vernon points out, the clustered index is the table. So another way of putting it is that your non-clustered index is duplicating the whole table.

0

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.