CODEWITHSUNDEEP
sql-server-2008database-indexescomposite-indexcovering-index
skye
skye

Reputation: 23

Composite Index and Covering Index in SQL Server

I have a table with (ID, col1, col2) & query of

SELECT ID FROM table WHERE col1 = 2
AND col2 = 7

Should I create a composite index

CREATE NONCLUSTERED INDEX IDX_Composite ON table (ID, col1, col2)

or covering index

CREATE NONCLUSTERED INDEX IDX_Covering ON table ID INCLUDED (col1, col2)

Upvotes: 1

Views: 742

Answers (1)

Thom A
Thom A

Reputation: 95949

Neither of the indexes in your question will help the query you have. The reason for this is because the first sorting column is the ID, however, your filtering columns are on col1 and col2; as such the index doesn't help the data engine quickly seek to the rows you need.

Let's put what you have into a classic phone book analogy. Let's say that the ID is the Phone Number, Col1 the Surname, and Col2 the Forename. If we use your index, the phone phone book is order by phone number, starting at the "lowest" phone number and ending at the "highest". If you want phone numbers for people called "John Smith" your only option is to go through the entire book and find every John Smith.

A traditional phone book, however, is ordered by the Surname, then Forename, and has the phone number for each beside them. This means that you can now easily service the question of "What are the phone numbers for John Smith"? Flick through the book to "Smith" first, then "John", and you have all the phone numbers.

So, what your index should look like is the following:

CREATE NONCLUSTERED INDEX IX_Composite ON table (col1, col2) INCLUDE (ID);

As noted by Charlieface as well, if your column ID is your clustered index then there is actually no need to INCLUDE it; the column(s) in the clustered index are automatically INCLUDEd in any NONCLUSTERED indexes.

Upvotes: 1

Related Questions