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

columnstorematerialized-viewsql server

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
);

Best Answer

If you can change the query itself to use the view + NOEXPAND, this can work:

Table definition and test data

CREATE TABLE [dbo].[myTable](
    [ItemId] [int] PRIMARY KEY NOT NULL,
    [BusinessDate] [date] NULL,
    [Gross] [decimal](18, 6) NULL
    );


INSERT INTO 
[dbo].[myTable] WITH(TABLOCK)
(BusinessDate,Itemid,Gross)
SELECT
TOP(5000000) CAST(getdate() AS DATE),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
11.2142
FROM master..spt_values
CROSS APPLY master..spt_values spt2;

View definition + indexes

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT 
            BusinessDate, 
            ItemId
            ,COUNT_BIG(*) AS NumberOfRows
            ,CAST(SUM(Gross) as [decimal](18, 6))  AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId
GO

CREATE UNIQUE CLUSTERED INDEX CX_ItemId
ON dbo.[SalesAggregated](ItemId);

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
[Gross],
BusinessDate

);

Query

SELECT SUM(Gross)
FROM [dbo].[SalesAggregated] WITH(NOEXPAND)
WHERE BusinessDate = '2019-08-14';

Result

enter image description here

Properties of the scan with the locally aggreggated rows & batch mode:

enter image description here

Querying the view + WITH(NOEXPAND) is necessary to make this example work.

There are many other reasons why you should use WITH(NOEXPAND). More reasons in this blogpost by Paul White

Related Question