I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table:
CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING AS SELECT [Category], [Year], SUM(ISNULL(Loss,0)) AS [Loss], COUNT_BIG(*) as [Count] FROM dbo.Sub_Portfolio_Losses GROUP BY [Category], [Year] GO CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio([Category], [Year]);
My original goals were more ambitious, but indexed views are so restrictive... I'd be happy enough just to get this to work.
Sadly, when I try to do a basic query on this indexed view:
SELECT TOP (100) * FROM Losses_CombinedPortfolio ORDER BY Loss DESC
...the query is just as slow, and the actual execution plan shows that it's always going back to the source table and computing the aggregations from scratch each time:
I can only assume that this is because my computed "Loss" column isn't getting materialized - but that would surprise me, since the clustered index creation succeeds.
Note that my primary use-case for this view is to sort by
Loss descending, but I couldn't manage to explicitly create an index that includes it:
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio (Category, Loss DESC, [Year]);
I get the error:
Cannot create index or statistics 'Idx' on view 'dbo.Losses_CombinedPortfolio' because key column 'Loss' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.
I tried resolving the "imprecise" by casting the summed loss to types other than
float (even tried truncating it to
bigint) but it seems this error is stemming from the underlying type used to compute the sum.
I'm confused - I've seen other questions claim that they were able to successfully perform aggregations such as
sum in their indexed views, so I'm not sure why this wouldn't work.