Two non-clustered indexes with the same attributes as key

user1532449 0
Viewed 51

I have two indexes like this.

Index #1:

CREATE NONCLUSTERED INDEX [index1] 
ON [dbo].[table1] ([column1], [column2], [column3])
INCLUDE ([column4], [column5], [column6]) WITH (ONLINE = ON)

Index #2:

CREATE NONCLUSTERED INDEX [index2] 
ON [dbo].[table1] ([column2], [column1], [column3]) 
INCLUDE ([column4], [column5]) WITH (ONLINE = ON)

Question is: since both indexes have the same columns (but with a different sequence) and index1 includes more columns that index2, would you say that index2 could be removed and all the queries that it was serving would be served by index1?

Any help will be greatly appreciated!

azure-sql-database sql-server
1 Answers

Would you say that index2 could be removed and all the queries that it was serving would be served by index1?

No, index1 will not serve the queries that used index2.

Index is sorted based on the columns/keys selected. So different key orders, regardless if multiple indexes contain the same set of columns as index key, will be used differently by the query optimizer.

But having duplicate indexes (indexes with the same set of columns/keys) is not optimal. I would identify the queries that use the indexes and rearrange the join conditions and where clause predicates so that it would use one index and can therefore eliminate the duplicate index.

LearnItDom0
Related