MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

by Lukas.Navratil   Last Updated August 14, 2019 12:06 PM

I have an indexed view with non-clustered columnstore index, e.g.

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
            ,COUNT_BIG(*) AS NumberOfRows
            ,SUM(Gross) AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId

Where Gross in underlying table is defined as decimal(18,6), so it supports aggregate pushdown. But result of the SUM in the view is decimal(38,6), which is too big for aggregate pushdown.

I can't cast it by using CAST(SUM(Gross) as decimal(18,6)) because that's not supported in indexed views, I get this error when I try to create unique clustered index:

Cannot create the clustered index 'UX_SalesAggregated' on view 'DemoDB.dbo.SalesAggregated' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Converting fields to money datatype in underlying table seems like one of the possible solutions, but I can't do that with all fields as some of them requires higher precision than 4 decimal points.

I also need the automatic indexed view matching working, so that the indexed view is automatically used if possible when the underlying table is queried. That probably wouldn't be possible when I would create a view that casts decimals to money in between the table and indexed view.

Is there any solution how to achieve that?

Related Questions

Create indexed view with fluctuating data behind

Updated April 19, 2019 08:06 AM

Replace a materialized view in Postgres

Updated December 15, 2017 11:06 AM