Difference between 2 indexes with columns defined in reverse order

Question

Are there any differences between following two indexes?

• IDX_IndexTables_1
• IDX_IndexTables_2

If there are any, what are the differences?

create table IndexTables (
    id int identity(1, 1) primary key,
    val1 nvarchar(100),
    val2 nvarchar(100),
)

create index IDX_IndexTables_1 on IndexTables (val1, val2)
GO

create index IDX_IndexTables_2 on IndexTables (val2, val1)
GO

Answer 1

Yes. There is a difference.

The composite index IDX_IndexTables_1 can be used for any query where the val1 column is used in the where clause.

The composite index IDX_IndexTables_2 can be used for any query where the val2 column is used in the where clause.

So, for instance IDX_IndexTables_2 cannot be used for this query (but IDX_IndexTables_1 can be used):

SELECT val1, val2 FROM IndexTables
WHERE val1 = some_value

but can be used for this query:

SELECT val1, val2 FROM IndexTables
WHERE val2 = some_value AND val1 = some_other-value

The way to think about a composite index is think about a paper telephone directory; It is indexed by the surname column, and then the firstname column: you can look up by surname but not by firstname on its own.

Answer 2

A multi-column index is conceptually no different than taking all the columns fields and concatinating them together -- indexing the result as a single field.

Since indexes are b-trees they are always searched left to right. You have to begin your search from the left to pair down results as you move to the right for the index to do its job and provide useful results.

With only a single field indexed:

WHERE val1 LIKE 'myvalue%' (uses index)
WHERE val1 LIKE '%myvalue' (cannot use index)

The same concept is applied for multi-column indexes:

When order is val1,val2

WHERE val1='value1' (uses index)
WHERE val2='value2' (cannot use index)

When order is val2,val1

WHERE val1='value1' (cannot use index)
WHERE val2='value2' (uses index)

If both fields are matched exactly order of indexes does not matter in that case.

WHERE val1='value1' AND val2='value2' (uses index in any order)