向视图添加索引时出错

Question

我已经使用以下代码创建了一个视图

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS 
  SELECT dbo.day_dim.date_time AS Date, 
         dbo.order_dim.quantity AS Target_Acheived
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id   
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time` 

现在当我使用时：

CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I ON two_weeks_performance (Date)

我收到错误：

无法创建索引，因为其选择列表未正确使用 COUNT_BIG()。考虑将 COUNT_BIG(*) 添加到选择中。

请帮我解决这个问题。

Answer 1

该错误准确地告诉您必须做什么 - 将 COUNT_BIG(*) 添加到您的选择列表中。

来自创建索引视图：

如果指定了 GROUP BY，则视图
选择列表必须包含
COUNT_BIG(*) 表达式和视图
定义不能指定HAVING，
汇总、多维数据集或分组集。

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS 
  SELECT dbo.day_dim.date_time AS Date, 
         dbo.order_dim.quantity AS Target_Acheived,
         COUNT_BIG(*) as Cnt 
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id   
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time
GO
CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I ON two_weeks_performance (Date)