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
– mohan111, Commented Mar 24, 2015 at 1: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
– Kritner, Commented Mar 24, 2015 at 1: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
– Simon_Weaver, Commented Oct 20, 2019 at 19:11

Ben Thul · Accepted Answer · 2022-07-27 01:16:58Z

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, 2022 at 1:16

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

answered Mar 24, 2015 at 1:50

zgirod

4,3793 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2673722 Over a year ago

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 Over a year ago

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 Over a year ago

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!

Kritner · Accepted Answer · 2015-03-24 01:38:31Z

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, 2015 at 1:38

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

6 Comments

user2673722 Over a year ago

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]

siride Over a year ago

@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?

zgirod Over a year ago

@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.

Kritner Over a year ago

what version of sql server is this? According to 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?

siride Over a year ago

@zgirod: Yep. That's a useful exception. Easy fix then.

|

Machavity · Accepted Answer · 2016-02-18 16:54:56Z

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, 2016 at 16:54

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

answered Feb 18, 2016 at 16:31

Gert

394 bronze badges

Comments

Simon_Weaver · Accepted Answer · 2021-04-14 01:04:12Z

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.

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related