5

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 restrive... 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:

actual execution plan

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.

12

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) 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 effectively is nondeterministic across different CPU architectures). So even casting it won't help you, you'd have to change the underlying type to something that is precise so that value is materialized in the table 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.)

3
  • 2
    Indeed, adding WITH (NOEXPAND) made use of the materialized index and performed about 100 times faster. Such a shame it couldn't come to that conclusion itself. Thanks for the answer. – Alain Dec 16 '20 at 14:31
  • 2
    @Alain don't feel too bad, the only reason I knew the answer so quickly was because I had to ask the same question twice on DBA.StackExchange before it finally stuck in my brain. :) I use indexed views rather often as well. The automation of not expanding is just a feature difference from Standard and Enterprise edition. Any other questions, feel free and I'll do my best to answer. Best of luck! – J.D. Dec 16 '20 at 15:30
  • @Alain if you pay a lot more for the Enterprise version it would save you having to use the NEXPAND option and even remove the need to use the indexed view at all in the query. – Ian Ringrose Dec 16 '20 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.