Are there any differences between following two indexes?
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
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.
What you have is a composite index. The order is important when your WHERE clause is not using all columns in the composite index.
Consider this query:
SELECT val1
FROM IndexTables
WHERE val1 = 'MyValue'
In order to know what index might be considered read from left to right the columns in your composite indexes. If the column doesn't exist in your query before you read all the columns in your query then the index won't be used.
IDX_IndexTables_1 (val1, val2): Reading from left to right val1 exists and it is our only column so this index would be considered
IDX_IndexTables_2 (val2, val1): Reading from left to right val2 doesn't exist in this query so it won't be used.
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)
The previous answers describe how to use the first column of each index. (in the where clause).
I think it's also important to point out that the second column is useful because it potentially increases performance of queries that involve the second column.
The following query will be completed with JUST an index seek on IDX_1, saving valuable lookups to the base table (since val2 is already part of the index).
SELECT val2 from IndexTables where val1 = @someVal1
Likewise, the reversed index will optimize this query:
SELECT val1 from IndexTables where val2 = @someVal2
However, only one (it doesn't matter which) of the two indexes is need to optimize the following query:
SELECT val1, val2 from IndexTables where val1 = @someVal1 and val2 = @someVal2
This shows that, depending on the queries your table receives, there may be a legitimate reason to have both indexes.
Other folk have answered that they are different, and I agree.
I'll add some other thoughts though...