Sql-server – Filtered index error when using two column condition

indexperformancesql server

I created a filtered index (in SQL Server 2012) and compared it with non-filtered index – I could see promising improvement as shown below. Now I need to change the filter condition to use two columns in the table. The required condition is WHERE InboundQuantity - OutboundQuantity <> 0 or InboundQuantity <> OutboundQuantity

But when I apply this filter, I am getting error message

“Incorrect WHERE clause for filtered index 'IX_WO_PlantCD_FilterQtyNotEqual'”.

I know there are limitations with filtered index. However is there a way to achieve this improvement with two column condition, using filtered index or the like?

Query

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0


--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

Plan

enter image description here

Best Answer

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view.

The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high concurrency.

The overhead of maintaining the view indexes is also typically less than for triggers.

For example, given a table:

CREATE TABLE dbo.MyTable
(
    PlantCD varchar(10) NOT NULL,
    InboundQuantity integer NOT NULL,
    OutboundQuantity integer NOT NULL
);
GO
-- Pretend the table has rows
UPDATE STATISTICS dbo.MyTable 
WITH ROWCOUNT = 1000000, PAGECOUNT = 50000;

A suitable indexed view might be:

CREATE VIEW dbo.MyTableQtyDiff
WITH SCHEMABINDING
AS
SELECT
    MT.PlantCD,
    QtyDiff = SUM(MT.InboundQuantity - MT.OutboundQuantity),
    NumRows = COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity
GROUP BY
    MT.PlantCD;
GO
CREATE UNIQUE CLUSTERED INDEX 
    CUQ_dbo_MyTableQtyDiff__PlantCD
ON dbo.MyTableQtyDiff
    (PlantCD);
GO
-- Pretend the view has some rows
UPDATE STATISTICS dbo.MyTableQtyDiff
WITH ROWCOUNT = 10000, PAGECOUNT = 50;

If you are running Enterprise Edition, automatic indexed view matching means a query written without referencing the view directly can still use it:

-- Enterprise Edition required
SELECT 
    SUM(MT.InboundQuantity - MT.OutboundQuantity)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity;

The execution plan shows the indexed view being accessed:

Execution plan 1

For other editions, you would need to reference the view directly, and use the NOEXPAND hint:

-- Any edition
SELECT 
    SUM(MTQD.QtyDiff)
FROM dbo.MyTableQtyDiff AS MTQD WITH (NOEXPAND);

Execution plan:

Execution plan 2

The same connection setting restrictions apply to indexed views as for indexed computed columns. You may choose to use the NOEXPAND version even in Enterprise Edition, since use of the view is guaranteed (not decided by the optimizer), and cardinality estimates will usually be better as well.

The effect of adding an indexed view depends on many factors, so you need to test it. Any safe solution has the potential to increase contention, but where an indexed computed column doesn't fit, an indexed view is often the next best option. If done well, the effect can be minimal. The overhead of an indexed view over a single table can be no more than that of adding a new nonclustered index to the base table.

Related Question