difference between creating index with two columns and creating separate index on two columns

Question

What is the difference between creating index with two columns and creating separate index on two columns?

Difference between

create nonclustered index ix_index1 on table1(col1)
create nonclustered index ix_index2 on table1(col2)

and

create nonclustered index ix_index1 on table1(col1, col2)

score 3 · Accepted Answer · answered Jul 04 '14 at 07:14

You might experience a difference if you have any queries that select based on col2 alone.

SELECT (list of columns)
FROM dbo.YourTable
WHERE col2 = 'someValue'

If you have two separate indices, then there is a chance that ix_index2 might be used to speed up this query.

However, if you only have a compound index on (col1, col2), then that index cannot ever be used for this query. A compound index can only ever be used if the n left-most columns are being referenced in a query.

So your compound index might be used

if your query uses both col1 and col2 in a WHERE clause
if your query uses only col1 in a WHERE clause

but it can NEVER be used if your query only uses col2 in the WHERE clause

Then your indexes are totally useless (unless you're using an `ORDER BY` clause that uses one of those columns - or both) — marc_s, Jul 07 '14 at 19:26

difference between creating index with two columns and creating separate index on two columns

1 Answers1