Creating an appropriate index for a frequently used query in SQL Server

Posted by Slauma on Stack Overflow See other posts from Stack Overflow or by Slauma
Published on 2010-06-13T16:55:41Z Indexed on 2010/06/13 17:02 UTC
Read the original article Hit count: 260

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server