0

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

  • An index on (invoiceid, notCountedInTotal) INCLUDE (accountAmount) will likely be used by this query. – ypercubeᵀᴹ May 22 '18 at 23:16
  • sure it would be used, and the index would even be more generic, but I wanted to take advantage of the percent of rows I could ignore and the reduced amount of disk space , plus personal preferences too, so I opted for the filtered index. that can be changed accordingly as to how the data is used. – marcello miorelli May 22 '18 at 23:22
  • 1
    If you are planning on using a filtered index just to support an aggregation query why not use an indexed view instead and precalculate the aggregation? – Martin Smith May 23 '18 at 6:57
  • That's a good point and I had considered it, but on this particular situation it was an emergency and I did not have the time to proper assess the cost of having an indexed view there (IO related waits or any process that might truncate any table or whatever, I know that this database is involved in replication too) so I decided to keep it simple enough to speed things up but I might revisit it later on, as I love indexed views (within limits) – marcello miorelli May 23 '18 at 8:12
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:

enter image description here

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.