In order to solve this problem, we can add a PERSISTED COMPUTED column on that table, e.g. ADD col1NullToZero AS ISNULL(col1, 0) PERSISTED. By making the computed column as persisted, we can create indexes on it (ref. Creating Indexes on Persisted Computed Columns). Then you can have an index able to cover the query. Surprise that even the query doesn't directly specify the computed column in its predicate, SQL Server still able to discover the benefit of using the index on the computed column.
Let's see an example below:
1. Create a table, called [MainTran], with two columns: TranID as its primary key, and a Nullable column DepositID that has an index.
CREATE TABLE [MainTran] (
[TranID] [int] NOT NULL PRIMARY KEY,
[DepositID] [int] NULL
)
GO
CREATE NONCLUSTERED INDEX [IX_MainTran_DepositID] ON [MainTran] (
[DepositID]
)
GO
2. Populate some rows into it. Some rows with concrete DepositID values, some DepositID are 0, some DepositID are NULLs.
3. The following query can be fulfilled by index seek, but the result is not correct:
4. The following query result is correct, but it's scan the whole table:
5. The following query gets more complicated execution plan and worse performance:
6. Create a PERSISTED COMPUTED column based on that filtering column, and add an index on it:
ALTER TABLE dbo.MainTran ADD DepositIdNullToZero AS ISNULL(DepositID, 0) PERSISTED
GO
CREATE INDEX IX_MainTran_DepositIdNullToZero ON MainTran (DepositIdNullToZero);
GO
7. Using the Computed column on the query, now it uses seek on the new index:
8. Even the query is using the original expression predicate, SQL Server still able to use the new index on the computed column!
