Indexed view not used in actual execution plan?

Question

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.

Answer 1

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server) in order for your query to use the indexed view (instead of expanding it to the underlying table). This will solve your first issue.

This is how you can use it in your query:

SELECT TOP (100) * 
FROM Losses_CombinedPortfolio WITH (NOEXPAND)
ORDER BY Loss DESC

You can see more information in the Query Hints docs by Microsoft. But this is the relevant section to what you're currently experiencing:

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

This is one of the few query hints you can utilize without worrying about it being bad practice, and actually it's recommended to use for certain reasons as well.

As far as your issue with the "imprecise" error, this is as you guessed it, due to the Loss column being of type FLOAT (which is not precise). Even casting it won't help you; you'd have to change the underlying type to something that is precise before you can index it in the view. (E.g. if you can change the data type to DECIMAL, then you'd be able to add that column to your index.)

From the documentation:

Any float or real expression is considered imprecise and cannot be a key of an index; a float or real expression can be used in an indexed view but not as a key. This is true also for computed columns. Any function, expression, or user-defined function is considered imprecise if it contains any float or real expressions. This includes logical ones (comparisons).

Answer 2

I had a similar issue. My view had a unique clustered index on the view but when executing it was still slow. When looking at the actual execution plan it was further providing me with an index creation hint which was odd because there was a clustered index available. It turned out that due to the version, standard edition, in use the query plan was performing an expand on the view so in effect bypassing the index and acting directly on the view and base tables. I had to provide an explicit 'with (noexpand)' query hint in order to force the use of the clustered index. I've read that enterprise and developer edition implicitly perform a noexpand on the query. This is something that only effects standard edition.