Best index(es) to use for an OR Statement in SQL Server

Question

I have a table which has a bunch of columns but the two relevant ones are:

Due_Amount MONEY
Bounced_Due_Amount MONEY

I have a SQL query like the following

SELECT * FROM table WHERE (Due_Amount > 0 OR Bounced_Due_Amount > 0)

Would the best index to put on this table for SQL Server 2008 be an index which includes both columns in the index, or should I put an separate index on each column?

Answer 1

Specifically for this query, it would be best to create an index on both columns in the order they are used in the where clause. Otherwise the index might not be used.

Answer 2

An Index can't be used on an OR like that. try this:

SELECT * FROM table WHERE Due_Amount > 0
UNION ALL  
SELECT * FROM table Bounced_Due_Amount > 0
--use "UNION" if Due_Amount and Bounced_Due_Amount could both >0 at any one time

have an index on Due_Amount and another on Bounced_Due_Amount.

It might be better to redesign your table. Without knowing your business logic or table, I'm going to guess that you could have a "Bounced" Y/N or 1/0 char/bit column and just a "Due_Amount" column. Add an index on that "Due_Amount" and the query would just be:

SELECT * FROM table WHERE Due_Amount > 0

you could still differentiate between a Bounced or not row. This will not work if you need to have both a bounced and non-bounced due amount at the same time.

Answer 3

My guess is that you would be better off with an index on each individual column. Having it on both won't help any more than having it on just the first column unless you have other queries that would use the compound index.

Your best bet is to try the query with an index on one column, an index on the other column, and two indexes - one on each column. Do some tests with each (on real data, not test data) and see which works best. Take a look at the query plans to understand why.

Depending on the specific data (both size and cardinality) SQL Server may end up using one, both, or possibly even neither index. The only way to know for sure is to test them each.

Answer 4

Technically, you can have an index on a persisted computed column and use the computed column instead of the OR condition in the query, see Creating Indexes on Computed Columns:

alter table [table] add Max_Due_Amount as
   case 
    when Due_Amount > Bounced_Due_Amount the Due_Ammount
    else Bounced_Due_Amount
   end
   persisted;
go

create index idxTableMaxDueAmount on table (Max_Due_Amount );
go

SELECT * FROM table WHERE Max_Due_Amount > 0;

But in general I'd recommend using the UNION approach like KM suggested.