Creating an appropriate index for a frequently used query in SQL Server
- by Slauma
In my application I have two queries which will be quite frequently used. The Where clauses of these queries are the following:
WHERE FieldA = @P1 AND (FieldB = @P2 OR FieldC = @P2)
and
WHERE FieldA = @P1 AND FieldB = @P2
P1 and P2 are parameters entered in the UI or coming from external datasources.
FieldA is an int and highly on-unique, means: only two, three, four different values in a table with say 20000 rows
FieldB is a varchar(20) and is "almost" unique, there will be only very few rows where FieldB might have the same value
FieldC is a varchar(15) and also highly distinct, but not as much as FieldB
FieldA and FieldB together are unique (but do not form my primary key, which is a simple auto-incrementing identity column with a clustered index)
I'm wondering now what's the best way to define an index to speed up specifically these two queries. Shall I define one index with...
FieldB (or better FieldC here?)
FieldC (or better FieldB here?)
FieldA
... or better two indices:
FieldB
FieldA
and
FieldC
FieldA
Or are there even other and better options? What's the best way and why?
Thank you for suggestions in advance!
