Filtered index error when using two column condition
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 &lt;&gt; 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 &lt;&gt; MT.OutboundQuantity;


The execution plan shows the indexed view being accessed:



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:



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.