Creating an index with a where clause - SQL SVR 2005

Question

I have recently discovered the ability to use WHERE clauses within indexes in SQL Server 2005. I would like to optimize some queries, and was hoping to get some feedback.

The table of interest contains 2 float columns, [long] and [short]. These columns could be 0 in 20-40% of rows. There are several stored procs that query this table with one of the following clauses:

• WHERE (long <> 0 OR short <> 0)
• WHERE (long <> 0 AND short <> 0)

I am considering putting indexes on long and short (or one index on both) with the condition WHERE <> 0 in the hopes that the stored procs will be able to use an index scan rather than a table scan to grab this data.

Given the above info, how would you go about creating indexes? Or, if an index isn't the best solution, what are reasonable alternatives? Thanks in advance.

Answer 1

Using Where clauses in an index creation is a SQL Server 2008 feature - Filtered indexes are not within SQL Server 2005.

Filtered Indexes follow the same rules about tipping as normal NC, non-covering indexes, so with 60-80% of the rows being <> 0, the likelihood is that the index will tip to a table / clustered index scan.

The only way to avoid that tipping is to make the index covering, but we can not see the rest of the clause / selection to know whether that is a reasonable option.