2

I am trying to use an expression in the SELECT statement to create an indexed view. According to the MDSN guidelines for Indexed views, expressions can be used if they are deterministic. Unfortunately, when I try to create a unique clustered index over the view scripted below, i get an error message shown below. The columns on which I am performing the expressions have been converted to the integer datatype, so there shouldn't be a problem of precision as well. Any idea why this expression is not working?

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.

ALTER VIEW [dbo].[Table2]
    WITH SCHEMABINDING
AS
SELECT [Manufacturer]
    ,SUM([QAV]) / COUNT_BIG(*) AS AvgQAV
    ,SUM([BackOrders$]) / COUNT_BIG(*) AS AvgBackorder$
    ,DATEPART(year, [Date]) AS Year
    ,DATEPART(month, [Date]) AS Month
    ,[fixSBU]
    ,[DC Name]
    ,COUNT_BIG(*) AS NumRows
FROM [dbo].[Copy_IOPS2]
GROUP BY [Manufacturer]
    ,DATEPART(year, [Date])
    ,DATEPART(month, [Date])
    ,[fixSBU]
    ,[DC Name]

Thanks!

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
user2673722
  • 295
  • 2
  • 6
  • 15

2 Answers2

1

The error message is telling you that it does not like the aggregate functions SUM and COUNT_BIG. These are non-deterministic because the number of rows in each Group returned by the GROUP BY may vary between executions.

Martin Brown
  • 24,692
  • 14
  • 77
  • 122
  • 1
    Why will SUM and COUNT_BIG be non deterministic? Given the same input, the expression will generate the same output always. – user2673722 Mar 24 '15 at 14:24
  • The only input you give to an aggregate function is just the column name. The output then varies by the row set that it looks up internally. Because this row set varies and you don't give it as input the output can't be determined just from your input. (That's not quite how it works under the hood but is the conceptual way to think of it). – Martin Brown Mar 24 '15 at 14:41
  • I used the columnproperty function to check if the column with the expression was deterministic and precise or not, it returned a 1 in both cases, indicating that the column was deterministic. – user2673722 Mar 24 '15 at 14:58
0

Try this ,

ALTER VIEW [dbo].[Table2] WITH SCHEMABINDING AS
SELECT [Manufacturer] ,
       SUM([QAV]),
       COUNT_BIG(*),
       SUM([BackOrders$]),
       COUNT_BIG(*) AS AvgBackorder$ ,DATEPART(YEAR, [Date]) AS YEAR ,
                                      DATEPART(MONTH, [Date]) AS MONTH ,
                                      [fixSBU] ,
                                      [DC Name] ,
                                      COUNT_BIG(*) AS NumRows
FROM [dbo].[Copy_IOPS2]
GROUP BY [Manufacturer] ,
         DATEPART(YEAR, [Date]) ,
         DATEPART(MONTH, [Date]) ,
         [fixSBU] ,
         [DC Name]
SELECT [Manufacturer] , SUM([QAV]) / COUNT_BIG(*) AS AvgQAV,
       SUM([BackOrders$]) / COUNT_BIG(*) AS AvgBackorder$, ,DATEPART(YEAR, [Date]) AS YEAR,
                                                            DATEPART(MONTH, [Date]) AS MONTH,
                                                            [fixSBU],
                                                            [DC Name] ,
                                                            COUNT_BIG(*) AS NumRows
Suraj Singh
  • 4,041
  • 1
  • 21
  • 36
  • 1
    Well, if the point was to show that they work separately, that's definitely true. The point is, even though the expression is deterministic, that particular error is coming up. Any idea why ? – user2673722 Mar 24 '15 at 14:28
  • @user2673722 I found something interesting, Have a look it may answer your curiosity to some extent https://social.msdn.microsoft.com/forums/sqlserver/en-US/1404e045-e6c5-4307-a458-c59eb8a67961/datepart-determinism – Suraj Singh Mar 24 '15 at 14:31
  • 1
    I don't think that's the issue, since the error message says there is a problem with the aggregate function not the DATEPART one. – user2673722 Mar 24 '15 at 14:34