During my application I've two queries which is often used. The Where clauses of those queries would be the following:

WHERE FieldA = @P1 AND (FieldB = @P2 OR FieldC = @P2)

and

WHERE FieldA = @P1 AND FieldB = @P2

P1 and P2 are parameters joined within the UI or originating from exterior datasources.

  • FieldA is definitely an int and highly non-unique, means: only two, three, four different values inside a table with say 20000 rows
  • FieldB is really a varchar(20) and it is "almost" unique, you will see only very couple of rows where FieldB might have a similar value
  • FieldC is really a varchar(15) as well as highly distinct, but less than FieldB
  • FieldA and FieldB together are unique (but don't form my primary key, the industry simple auto-incrementing identity column having a clustered index)

I am wondering now what's the easiest method to define a catalog to accelerate particularly both of these 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 exist even other and options? What's the easiest way and why?

Appreciate suggestions ahead of time!

Edit:

Just like a data with other visitors: Here was another answer that has been erased now. Really the solution appeared very helpful in my experience. The recommendations ended up being to create two indices (based on my second item above) and also to reformulate the very first query using a UNION of two choose claims (one with WHERE FieldA = @P1 AND FieldB = @P2 and something with WHERE FieldA = @P1 AND FieldC = @P2) rather than OR to profit from both indices (which wouldn't be using the OR-operator).

Edit2:

The statement by using Or even the indexes aren't used which a UNION is more suitable appears to become wrong - a minimum of based on my very own tests (see my very own answer below).

Stretching Remus' (edit: now erased) answer...

  • if @p2 is varchar(15) then you definitely can't compare against FieldB dependably, it's varchar(20)
  • if @p2 is varchar(20) then FieldC is going to be transformed into varchar(20) and never make use of an index (or otherwise scan it)
  • if @p1 has only 2, 3, 4 values then why don't you tinyint and lower table/index size?

I wouldn't make use of indexes before you resolve this datatype priority problem: this really is on the top from the OR clause problem.

Finally, a column is exclusive or non-unique: there's no between. Statistics help here with selectivity, but it is irrelevant.

I'd turn back indexes from Remus' response to be FieldB, FieldA (and different) and FieldC, FieldA due to FieldA's selectivity

Edit, after comments: you cannot compare using @p2 against using constant strings.