What is the difference between these 2 index setups?

What would be the difference between these 2 setups for an index on Field1 and Field2 on TableA?

Option 1:

create nonclustered index IX_Index1 on TableA (Field1)
create nonclustered index IX_Index2 on TableA (Field2)

Option 2:

create nonclustered index IX_Index1 on TableA (Field1, Field2)

in Option 1, you will have two separate indexes, sorted by Field1 and Field2, respectively

in Option 2, you will have one index, sorted by Field1, and then by Field2 columns:

Option 2:

Field1, Field2

A A
A B
A C
B A
B B
B C
C A
C B
C C

Option 2 will be good for queries, that are filtering both by Field1 and Field2 in the WHERE clause

If some queries are filtering just by Field1, Option2 will work for them too (you probably can get Index Seek), but not as good (more logical reads) compared to if there was separate index on Field2.

Look at your typical queries and make a decision based on them, whether you want fields combined or in separate indexes. Or you can have all index variants, if table is not too big

StackExchange Database Administrators Q#326097, answer score: 3