CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Michael J. Gray
Michael J. Gray

Reputation: 9916

If I place a composite index on three columns and use them in the same query but in different places, will it still be effective?

With the following table and index:

CREATE TABLE [Ticket]
(
    [Id] BIGINT IDENTITY NOT NULL,
    [Title] CHARACTER VARYING(255) NOT NULL,
    [Description] CHARACTER VARYING(MAX) NOT NULL,
    [Severity] INTEGER NOT NULL,
    [Priority] INTEGER NOT NULL,
    [CreatedOn] DATETIMEOFFSET NOT NULL,

    PRIMARY KEY([Id])
);

CREATE INDEX [Ticket_Priority_Severity_CreatedOn_IX] ON [Ticket]([Priority], [Severity], [CreatedOn]);

Will the following query:

SELECT [Id]
FROM [Ticket]
WHERE [Priority] = 1
ORDER BY [Severity] DESC, [CreatedOn] ASC

make use of the entire composite index or only utilize the [Priority] part of the index?

I know that for a query that had all of the columns in the WHERE clause, the whole index would be used. I am unsure about the above case though!

Given the actual execution plan below, on a table with no statistics, I am not sure how to interpret it.

Actual Execution Plan

It does look like it used the index, but which parts? There is clearly a sort cost, but is that sorting by [Severity] and then [CreatedOn] after doing a seek on [Priority]?

Upvotes: 1

Views: 50

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181017

It may use the index, but it will only use the Priority part efficiently since you have the index sorted in a way that is not optimal for the query;

ORDER BY [Severity] DESC, [CreatedOn] ASC

vs 

CREATE INDEX [Ticket_Priority_Severity_CreatedOn_IX] ON 
  [Ticket]([Priority], [Severity], [CreatedOn]);

As you can see in this fiddle if you click the execution plan, the query is split into an index seek and a sort.

Since Severity is sorted ascended, the index won't be (optimally) used for the sort. If you really want an optimal sort, index Severity descending as your query uses it;

CREATE INDEX [Ticket_Priority_Severity_CreatedOn_IX] ON 
  [Ticket]([Priority], [Severity] DESC, [CreatedOn]);

An SQLfiddle with the fixed index. Note that the whole query is now an index seek.

Note that the plan may look different for you depending on your data, but in general this is true, an index sorted the same way as the query accesses it will use the index better.

Upvotes: 2

Related Questions