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
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.
For example, I want to achieve that queries like
SELECT SUM(Gross)
FROM myTable
WHERE BusinessDate = '2019-08-14';
Will use columnstore index scan on the indexed view and that the aggregation will be done within this scan. That isn't now possible because the Gross in SalesAggregated is decimal(38,6) which doesn't support aggregate pushdown. As a result, query optimizer (incorrectly) rather scans columnstore from unaggregated table where aggregate pushdown is possible.
Is there any solution how to achieve that?
UPDATE
So the problem is only when the PK column from the underlying table isn't part of the GROUP BY in indexed view definition. Example below from Randi works, but following code fails on the error mentioned above:
CREATE TABLE [dbo].[myTable](
[ItemId] [int] PRIMARY KEY NOT NULL,
[BusinessDate] [date] NULL,
[Gross] [decimal](18, 6) NULL
);
GO
CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
SELECT
BusinessDate
,COUNT_BIG(*) AS NumberOfRows
,CAST(SUM(Gross) as [decimal](18, 6)) AS Gross
FROM [dbo].[myTable]
GROUP BY BusinessDate
GO
CREATE UNIQUE CLUSTERED INDEX CX_BusinessDate ON dbo.[SalesAggregated](BusinessDate);
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
[Gross],
BusinessDate
);
SELECT SUM(Gross) FROM myTable WHERE BusinessDate = '2019-08-14'
will use columnstore index scan on the indexed view and that the aggregation will be done within this scan. That isn't now possible because the Gross inSalesAggregated
is decimal(38,6) which doesn't support aggregate pushdown. As a result, query optimizer (incorrectly) rather scans columnstore from unaggregated table where aggregate pushdown is possible.