SQL Server索引视图:无法在视图的SELECT语句中使用表达式

Question

我正试图在SELECT语句中使用一个表达式来创建一个索引视图。根据MDSN的索引视图指南，如果表达式具有确定性，则可以使用它们。不幸的是，当我试图在下面脚本化的视图上创建一个唯一的聚集索引时，我收到了如下所示的错误消息。我正在执行表达式的列已转换为integer数据类型，因此不应该存在精度问题。知道为什么这个表达不起作用吗？

无法创建聚集索引".."在视图".."上因为视图的选择列表包含关于聚合函数或分组列的结果的表达式。请考虑从选择列表中删除聚合函数或分组列的结果上的表达式。

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]

谢谢！

Answer 1

错误消息告诉您它不喜欢聚合函数SUM和COUNT_BIG。这些是不确定的，因为Group by返回的每个组中的行数可能在执行之间有所不同。

Answer 2

试试这个，

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