sql-serverclustered-indexindexed-view

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


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.


Solution

  • 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:

    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.