sql-serversql-server-2008indexing

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

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 a separate index on each column?

Solution

  • 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.