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
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
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.
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.
17 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index