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
-- Pretend the table has rows
UPDATE STATISTICS dbo.MyTable
WITH ROWCOUNT = 1000000, PAGECOUNT = 50000;
A suitable indexed view might be:
CREATE VIEW dbo.MyTableQtyDiff
QtyDiff = SUM(MT.InboundQuantity - MT.OutboundQuantity),
NumRows = COUNT_BIG(*)
FROM dbo.MyTable AS MT
MT.InboundQuantity <> MT.OutboundQuantity
CREATE UNIQUE CLUSTERED INDEX
-- 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
SUM(MT.InboundQuantity - MT.OutboundQuantity)
FROM dbo.MyTable AS MT
MT.InboundQuantity <> 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
-- Any edition
FROM dbo.MyTableQtyDiff AS MTQD WITH (NOEXPAND);
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.