How to index for inequality queries?

Question

I have query that is excluding data based on the value of a float column

select * 
from My_Table
where my_Float_column != 0 and my_Float_column is not null

I don't want to index a float type if I can help it. Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

CREATE NONCLUSTERED INDEX IX_My_Table_Float_Filtered
    ON My_Table (my_Float_column)
    WHERE my_Float_column = 0 or my_Float_column is null

Answer 1

Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

No. There's no direct support in SQL Server for the sort of single-operator row-rejection you seem to have in mind.

Speaking more generally, the filtered index is not directly helpful for the given query, and isn't a valid index filter definition anyway. You could create an indexed view containing those predicates instead, but it still wouldn't be helpful as way to locate rows for the target query. You could rewrite the query to use the indexed view to exclude rows fetched with a separate full scan, but it's tough to see how that would be a good idea in general.

The closest you could get would probably be a scan of the indexed view populating a bitmap filter, with that filter applied as part of a full scan of the target table. It might be challenging to get this query plan shape reliably.

You could also use an indexed persisted computed column using a CASE expression, but again this would require rewriting the original query, and would require storage for every row in the table, plus the index.

From the information given, it seems the best you can do is to define the index as:

CREATE NONCLUSTERED INDEX IX_dbo_My_Table__Float_Filtered
ON dbo.My_Table (my_Float_column)
WHERE 
    my_Float_column <> 0 
    AND my_Float_column IS NOT NULL;