I got involved in a project to optimize a database. The primary table, Receivables, has 3 fields that we mainly use in query conditions. The primary key which is a auto incrementing int, is rarely used in our queries. It is also currently set as the clustered index. The 3 fields I'm considering using in a clustered index are year, quarter, and customer id. For normal operations I don't think adjusting the indexes would have any affect. However, since there's around 50,000 records per quarter, reports tend to take a minimum of 10 seconds to generate and will take a long time when creating a report that compares quarters from several years.

An example query for a report for several years would look something like

Code:
SELECT *
FROM   Receivables
WHERE (Year = 2006 AND Quarter = 2)
            OR (Year = 2007 AND Quarter = 2)
            OR (Year = 2008 AND Quarter = 2)
So would creating a clustered index on Year,Quarter,CustomerId make a significant improvement or should I attempt a different approach?

Also, this table has about 3 million records and takes up about 500 mb. So when I create this index will this table be locked for a few seconds, minutes, hours, or would access just be slow until the index is created?


Thanks,