I have an indexed view with non-clustered columnstore index, e.g.
CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,COUNT_BIG(*) AS NumberOfRows ,SUM(Gross) AS Gross FROM [dbo].[myTable] GROUP BY BusinessDate, ItemId
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?