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: 264
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