a filtered index to support a query - is it possible to consider nulls?

Question

I have been dealing with this query:

SELECT @amount = sum(accountAmount)  
FROM dbo.accountTransaction  
WHERE invoiceID = @invoiceID  
 AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL)   

ans as I have had good experiences with filtered indexes

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?

is there a work around for when you want to put an OR inside a filtered index?

would I not get into the same issue as below?

Unable to create a Filtered Index on a Computed Column

Answer 1

The solution I found on this occasion, having a look at the data, constraints, applications etc, I could change the column notCountedInTotal to not null having a default zero, as you can see on the table definition below.

CREATE TABLE [dbo].[accountTransaction] ( 
[accountTransactionID]     INT              IDENTITY(1,1)   NOT NULL,
[accountCode]              VARCHAR(30)                          NULL,
[transactionType]          CHAR(3)                              NULL,
[transactionReason]        VARCHAR(100)                         NULL,
[applicationID]            INT                                  NULL,
[invoiceID]                INT                                  NULL,
[entityID]                 INT                                  NULL,
[entityType]               CHAR(1)                              NULL,
[transactionCurrencyCode]  CHAR(3)                              NULL,
[transactionAmount]        DECIMAL(10,2)                        NULL,
[accountCurrencyCode]      CHAR(3)                              NULL,
[accountAmount]            DECIMAL(10,2)                        NULL,
[chequeNo]                 VARCHAR(20)                          NULL,
[issuingBank]              VARCHAR(50)                          NULL,
[issuingCountryCode]       CHAR(2)                              NULL,
[STReference]              VARCHAR(30)                          NULL,
[paymentMethod]            VARCHAR(10)                          NULL,
[GPStatus]                 INT                                  NULL,
[transactionDate]          DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_transactionDate] DEFAULT (getdate()),
[transactionStatus]        VARCHAR(10)                          NULL,
[userID]                   INT                                  NULL,
[parentID]                 INT                                  NULL,
[transactionComment]       VARCHAR(max)                         NULL,
[notCountedInTotal]        BIT                              NOT NULL  CONSTRAINT [DF_accountTransaction_notCountedInTotal] DEFAULT ((0)),
[enteredDate]              DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_enteredDate] DEFAULT (getdate()),
[chequeDetails]            VARCHAR(1000)                    NOT NULL  CONSTRAINT [DF_accountTransaction_chequeDetails] DEFAULT (''),
CONSTRAINT   [PK_accountTransaction]  PRIMARY KEY CLUSTERED    ([accountTransactionID] asc) WITH FILLFACTOR = 100)

After that it was easy to add a filtered index because there is no more the either the value is null or the value is zero thing.

execution plan using the newly created index:

Answer 2

Yes, it would be nice to create a computed column, persisted on notCountedInTotal column(that would put the zeros and nulls together) as below:

ALTER TABLE [dbo.accountTransaction]    ADD [notCountedInTotal1] AS (ISNULL(notCountedInTotal,0)) PERSISTED;

create an index on that column as below:

CREATE NONCLUSTERED INDEX F_invoiceID_notCountedInTotal1  ON dbo.accountTransaction (  INVOICEID, notCountedInTotal1 )  INCLUDE ( accountAmount)

Your select query would then be as simple as:

SELECT @amount = sum(accountAmount)  
FROM dbo.accountTransaction  
WHERE invoiceID = @invoiceID  
  AND notCountedInTotal1 = 0 

It will utilize the index F_invoiceID_notCountedInTotal1 with index seek, query will perform very well for huge amount of data, provided the temp DB has sufficient space for aggregation.