Index stragergy - one clustered index and one covering index

Question

Firstly please forgive my index ignorance!

I am looking at a table which has lots of single column indexes.

Would it not be better to have one clustered index and one non clustered index covering all the columns in the table?

Regards,

Ben

Answer 1

Hi @Benjamin Shaw ,

Non-clustered index is independent of the existence of the table. In other words, the non-clustered index will take up additional space. If the non-clustered index covers all columns, it will cause a lot of waste of space.

If you need to include multiple columns in the index, you can use a covering index or composite index, so that you can only put the columns you need into the non-clustered index. Avoid unnecessary space and efficiency waste.

For compound index and covering index, please refer to:
SQL Server Index – IV (Composite and Covering Index)

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

What would be the best depends on how the table is used, so that part we cannot answer. But what we can say that the two are not equivalent.

Say that you have the columns A, B, C, D, E and F. You have a clustered index on column A, and a single non-clustered index on columns B, C, D, E and F. Say that you have these queries:

SELECT * FROM tbl WHERE B = @value
SELECT * FROM tbl WHERE C = @value

Only the first query can use the non-clustered index efficiently. That is, find the matching values for B by quickly descending the index tree. For the second query, SQL Server needs to scan the index from start to end, because the matching values for C are scattered all over the index. (Since the index is primarily sorted on B.)

Now, consider this query:

SELECT * FROM tbl WHERE B = @value1 AND D = @value2

If you only have single-column indexes, SQL Server can seek the two indexes and then join the result, but it would have to read non-matching values in both indexes, so there would be some extra work. And in the end the optimizer may decide that a single scan is better.

On the other hand, if there is a composite index on B and D, SQL Server can again use the index tree to find the matching values in the most efficient way. And note here that the index must have B and D as the first columns. There may be more columns than these two, but an index on B, C and D in that order would be less useful, as again the matching values for D would be scattered among different values for C.

To conclude: to determine the best indexes for a table, you need to know the table is used. I would say that none of the strategies you outline - lots of single-column indexes and one single fat NC index - are likely to be correct, but, again, it all depends on how that table is used.