CODEWITHSUNDEEP
sqlsql-serverindexingview
Nick
Nick

Reputation: 307

Creating Index on a View with group by

So I'm trying to create an index on the example view I have here. At first I had the COUNT_BIG error so I added that in but now I'm this getting error

Cannot create the clustered index ... because the select list of the view contains an expression on result of aggregate function or grouping column.

SELECT        
SALE_DATE, 
DATEPART(YEAR, SALE_DATE) AS SALE_YEAR, 
CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, SALE_DATE), 0) AS DATE) AS SALE_MONTH, 
CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, SALE_DATE), 0) AS DATE) AS SALE_QUARTER, 
REGION, 
A_ID, 
B_ID, 
ISNULL(SUM(SALE_VALUE), 0) AS SALE_VALUE, 
ISNULL(SUM(ADV_COMM), 0) AS ADV_COMM, 
ISNULL(SUM(CASH_COMM), 0) AS CASH_COMM, 
...
and a load more SUM columns in the exact same format
...
COUNT_BIG(*) AS COUNT 
FROM dbo.sale_data 
GROUP BY SALE_DATE, REGION, A_ID, B_ID

CREATE UNIQUE CLUSTERED INDEX CIX_View_daily_sale_data 
ON view_daily_sale_data_test(SALE_DATE, A_ID, B_ID, REGION);

I have tried everything to work out what is actually wrong with this, removing group by, changing the columns that I'm indexing, removing the CAST's, I've read a lot of places you can include SUM by as long as you include COUNT_BIG.

Can someone just explain what I'm doing wrong here. Thanks!

Upvotes: 0

Views: 1342

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

The error message isn't too hard to decode:

contains an expression

ISNULL(...)

on result of aggregate function

SUM(...)

So, you're not allowed to do anything further to the result of an aggregate function.

Here, what I'd suggest is to create one view that just performs the SUM() operations, and is indexed, and then, if necessary, another view that just queries this first view, isn't indexed, and contains the ISNULL() expressions. Querying this second view (if appropriate hints are used or you're on a good SKU) should still massively benefit from the indexes on the first view.

Upvotes: 2

Related Questions