SQL Server Indexed Views: Cannot create clustered index because the select list contains an expression on result of aggregate function

Question

I am trying to create a simple indexed view on the query below. But when I try to create a unique clustered index on it, I get the following error:

Cannot create the clustered index '..' on view '..' 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.

The query I used is as follows:

SELECT 
    [Manufacturer]
    ,ISNULL(SUM([QAV]),0) as AvgQAV
    ,ISNULL(SUM([BackOrders$]),0)as AvgBackorder$
    ,DATEPART(year,[Date])as Year
    ,DATEPART(month,[Date])as Month
    ,[fixSBU]
    ,[DC Name]
FROM [dbo].[TABLE1]
Group By
    [Manufacturer]      
    ,DATEPART(year,[Date])
    ,DATEPART(month,[Date])
    ,[fixSBU]
    ,[DC Name]

Could anyone tell me the possible cause for this? As you can see I am already using the ISNULL function.

You cannot create an index based on aggregated data (i.e. SUM(BackOrders$)) because then every time you modified the underlying table, the index would have to re-group/calculate and then update. You could create the index on the underlying Rating table with the columns you are using (fixSBU and [DC Name]) and this should help things if the table is large otherwise forget the index. — mohan111, Mar 24 '15 at 01:43
@mohan111 though the underlying data might change, sum is still a deterministic function - as it returns the same results each time the same underlying data is provided. I think this should still work (at least due to the sum) — Kritner, Mar 24 '15 at 01:50
Just to clarify you 100% can create an index based on aggregated data. You have to be careful - SELECT SUM(Total) - SUM(Discount) AS SubTotal is not allowed, but SELECT SUM(Total - Discount) AS SubTotal is. — Simon_Weaver, Oct 20 '19 at 19:11

score 5 · Accepted Answer · edited Jul 27 '22 at 01:16

5

Here is a link to all the restrictions of an index view: https://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions

From the documentation these two items should stick out:

If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.
If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Also, you need to change your ISNULL statements. Right now you have ISNULL(SUM([BackOrders$]),0) and it should be SUM(ISNULL([BackOrders$], 0)). You need to SUM the ISNULL, not the other way around.

edited Jul 27 '22 at 01:16

Ben Thul

31,080
4
45
68

answered Mar 24 '15 at 01:50

zgirod

4,189
3
28
36

I've tried using something like this - `ISNULL(SUM([QAV]),0)/COUNT_BIG(*) as AvgQAV` in the SELECT statement too. That doesn't work either. I'll try the ISNULL modification you suggested too. Thank you! – user2673722 Mar 24 '15 at 02:05
I changed the ISNULL statement and introduced another COUNT_BIG(*) in the query, but I still get the same error. ` SELECT [Manufacturer] ,SUM(ISNULL([QAV],0))/COUNT_BIG(*) as AvgQAV ,SUM(ISNULL([BackOrders$],0))/COUNT_BIG(*)as AvgBackorder$ ,DATEPART(year,[Date])as Year ,DATEPART(month,[Date])as Month ,[fixSBU] ,[DC Name] ,COUNT_BIG(*) AS NumRows FROM [Table1] Group By [Manufacturer] ,DATEPART(year,[Date]) ,DATEPART(month,[Date]) ,[fixSBU] ,[DC Name]` – user2673722 Mar 24 '15 at 12:04
1

I got rid of the expression in the above query `(SUM(ISNULL([BackOrders$],0))/COUNT_BIG(*))`, altered ISNULL and created a separate COUNT_BIG(*) -- That worked! Thanks a lot! – user2673722 Mar 24 '15 at 12:25

score 0 · Answer 2 · answered Mar 24 '15 at 01:38

0

Doesn't make a whole lot of sense (at least not to me) but reference: https://msdn.microsoft.com/en-us/library/ms191432.aspx

Specifically:

If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

try adding a COUNT_BIG(*) to your select list and give it a whirl.

answered Mar 24 '15 at 01:38

Kritner

13,557
10
46
72

I've tried using COUNT_BIG(*) too. That doesn't work either. As soon as I remove the sum function from SELECT, I am able to create the unique clustered index, but aggregations are key here. `SELECT [Manufacturer] ,ISNULL(SUM([QAV]),0)/COUNT_BIG(*) as AvgQAV ,ISNULL(SUM([BackOrders$]),0)/COUNT_BIG(*)as AvgBackorder$ ,DATEPART(year,[Date])as Year ,DATEPART(month,[Date])as Month ,[fixSBU] ,[DC Name] FROM [Table1] Group By [Manufacturer] ,DATEPART(year,[Date]) ,DATEPART(month,[Date]) ,[fixSBU] ,[DC Name]` – user2673722 Mar 24 '15 at 01:46
@user2673722: seems pretty straightforward to me. You can't use aggregate functions in indexed views. It's in the docs and the error message is clear. What more can we do? – siride Mar 24 '15 at 01:54
@siride you can use aggregates in an index view. One of them just has to be a COUNT_BIG and he needs to SUM the ISNULL opposed to ISNULL the SUM. – zgirod Mar 24 '15 at 01:56
what version of sql server is this? According to https://technet.microsoft.com/en-us/library/cc917715.aspx starting in sql server 2005 sum can be used in indexed views, are you running 2000 or earlier? – Kritner Mar 24 '15 at 01:57
@zgirod: Yep. That's a useful exception. Easy fix then. – siride Mar 24 '15 at 01:58
Its sql server 2012. And one of the utilities of indexed views is to pre-aggregate data to improve query performance. – user2673722 Mar 24 '15 at 02:02

score 0 · Answer 3 · edited Feb 18 '16 at 16:54

0

I had a similar problem. One of my select fields looked like this:

sum(Pa * (CTRatio1a/CTRatio2a) * (VTRatio1/VTRatio2)* Polarity * [Percentage])/1000.0

By including the last division by 1000 in the bracket, it resolved the problem:

sum(Pa * (CTRatio1a/CTRatio2a) * (VTRatio1/VTRatio2)* Polarity * [Percentage]/1000.0)

edited Feb 18 '16 at 16:54

Machavity

30,841
27
92
100

answered Feb 18 '16 at 16:31

Gert

39
4

score 0 · Answer 4 · answered Apr 14 '21 at 01:04

Tip: It's better to have a real date field in the database and not just Year / Month. That way you can create a date index in addition to the clustered index.

However if you have FullDate, Year and Month you can get the same error message view contains an expression on result of aggregate function or grouping column.

That error can occur if you do this:

SELECT 

    [Manufacturer]  
    ,[Date] as FullDate
    ,DATEPART(year,[Date]) as Year
    ,DATEPART(month,[Date]) as Month

    ,COUNT_BIG(*) as Count
    ,SUM(OrderValue) as TotalOrderValue

FROM [dbo].[TABLE1]
Group By

    [Manufacturer]      

    ,[Date]
    ,DATEPART(year,[Date])
    ,DATEPART(month,[Date])

While not immediately obvious what's going on I assume this is because it looks at Date in the grouping columns and finds Date used in other columns (for the year and month). Clearly though this should logically work and you should be able to group like that.

I found a trick that got it working:

SELECT 

    [Manufacturer]  
    ,DATEADD(day, 0, [Date]) as FullDate
    ,DATEPART(year,[Date])as Year
    ,DATEPART(month,[Date])as Month

    ,COUNT_BIG(*) as Count
    ,SUM(OrderValue) as TotalOrderValue

FROM [dbo].[TABLE1]
Group By

    [Manufacturer]      

    ,DATEADD(day, 0, [Date])
    ,DATEPART(year,[Date])
    ,DATEPART(month,[Date])

This tricked the parser into allowing it, and now I can create a separate index (after the clustered) to search by FullDate.

Bonus: The real reason I stumbled upon this was because I needed ISO_WEEK and ISO_YEAR which are expensive to calculate. Here here's my final full list of grouping clauses I'm using for that:

-- date
DATEADD(day, 0, [Order].OrderDateDt) as OrderDateDt, -- trick! add 0 days to date 

-- day / month / year / quarter
DATEPART(day, [Order].OrderDateDt) as OrderDate_day,
DATEPART(month, [Order].OrderDateDt) as OrderDate_month,
DATEPART(year, [Order].OrderDateDt) as OrderDate_year,
DATEPART(quarter, [Order].OrderDateDt) as OrderDate_quarter,

-- iso week
DATEPART(iso_week, [Order].OrderDateDt) as OrderDate_isoweek,
YEAR(DATEADD(day, 26 - DATEPART(iso_week, [Order].OrderDateDt), [Order].OrderDateDt)) as OrderDate_isoyear

Make sure to include all these exactly the same in the SELECT and GROUP BY.

Note: This may have added complications with updating the index - so make sure to check the query plan you're getting for insert and updates. — Simon_Weaver, Apr 30 '21 at 09:06

SQL Server Indexed Views: Cannot create clustered index because the select list contains an expression on result of aggregate function

4 Answers4