I have been dealing with this query:
SELECT @amount = sum(accountAmount) FROM dbo.accountTransaction WHERE invoiceID = @invoiceID AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL)
I can create this one:
CREATE NONCLUSTERED INDEX F_INVOICEID ON dbo.accountTransaction ( INVOICEID ) INCLUDE ( accountAmount) where (notCountedInTotal is null)
But that would not solve my query problem above. So I would like to have the following filtered index:
CREATE NONCLUSTERED INDEX F_INVOICEID ON dbo.accountTransaction ( INVOICEID ) INCLUDE ( accountAmount) WHERE (notCountedInTotal IN (null,0))
Msg 10620, Level 16, State 1, Line 38 Filtered index 'F_INVOICEID' cannot be created on table 'dbo.accountTransaction' because the filter expression contains a comparison with a literal NULL value. Rewrite the comparison to use the IS [NOT] NULL comparison operator to test for NULL values.
is it at all possible?
would it be worth creating a computed column, persisted, that would put the zeros and nulls together and create the index on that?
would I not get into the same issue as below?