Sql-server – a filtered index to support a query – is it possible to consider nulls

filtered-indexindexindex-tuningsql serversql-server-2016

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

Best Answer

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:

enter image description here

Related Question