CODEWITHSUNDEEP
sql-server
adinas
adinas

Reputation: 4550

Two indexes with a few columns or One index with all columns

After Using the "Display Estimated Execution Plan" on two queries, I ended up creating two indexes as follows:

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table
(colA,colB,colC)

and

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table
(colA,colD,colE)

I noticed that creating just one index (replacing the above two):

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table
(colA,colB,colC,colD,colE)

also optimizes my queries.

So my question is, is it correct that the index with all the columns optimizes as well as two separate ones or is one method preferable to the other.

Thanks

Upvotes: 0

Views: 103

Answers (3)

Mutation Person
Mutation Person

Reputation: 30498

In answer to your question, no the two optimisations are not equivalent.

As for whether one method is preferred above another, it really depends on what your queries are, and how you modify the data.

If you have a query with a specific where clause on (ColA, ColB, ColC) and another on (ColA, ColD, ColE), then the two indexes will serve that scenario well.

However if you are updating the table a lot then you will have the additional overhead of maintaining two indexes.

As with anything like this, you are best to undertake permormance analysis and obtain metrics based on a pattern of expected access.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294237

is it correct that the index with all the columns optimizes as well as two separate ones

No, absolutely not. More likely your query needed an index on (colA) INCLUDE (colB, colC, colD, colE), or perhaps colA is actually a good clustered key candidate. This is a generic case you present and no real conclusion can be drawn. But, as a general rule, indexes on different columns serve different roles and can be used by different queries. An index on (A, B, C) can by no means substitute the need for an index on (B, C), (B, D) or (C, E). Even different order of columns cannot be substituted, an index on (A, B, C) is different and cannot substitute from an index on (B, C, A).

I recommend you go over the topic of Designing Indexes before anything else. After you read it, feel free to ask more questions.

Upvotes: 2

Mark Sowul
Mark Sowul

Reputation: 10600

To answer your question simply, order is important (think of a multi-level tree) so a combined index will not help with a query that only searches ColA, ColD, and ColE and not ColB and ColC.

So, for example, if you had a query like SELECT colA, colD, colE FROM dbo.my_table WHERE colA = 1 AND colD = 2

the second (combined) index would help somewhat (with the 'ColA' predicate) but the database would still have to scan the remaining results for colD = 2.

On the other hand, the combined index will take up less overall space, and have less of an impact on updates to column A, so you will have to weigh the tradeoffs.

Upvotes: 1

Related Questions