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)

marc_s · Accepted Answer · 2014-07-04 07:14:41Z

3

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

answered Jul 4, 2014 at 7:14

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emman Bangis Over a year ago

What if the query do not use where clause at all?

marc_s Over a year ago

Then your indexes are totally useless (unless you're using an ORDER BY clause that uses one of those columns - or both)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related