4

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
);
3
  • 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. Aug 14, 2019 at 13:20
  • 1
    Could you add a minimal, reproducable, complete example? This makes testing a lot easier. You could create this in db<>Fiddle but there are many other options. Other valuable information could be: table definition, the index create statements on the view, the execution plan, .... Aug 14, 2019 at 13:55
  • 2017 Developer Edition Aug 14, 2019 at 20:01

1 Answer 1

3

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

2
  • So the problem isn't in the partitioning, it doesn't work when the PK of underlying table isn't included in GROUP BY. Please see updated question for sample code that fails. Aug 14, 2019 at 21:07
  • And it quite makes sense, when you include the PK in GROUP BY, there is no aggregation, so you can manipulate the "aggregation result" any way you want, but when PK is not included, it's really an aggregation and according to the error message it's not allowed to manipulate aggregation result. Aug 14, 2019 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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