向视图添加索引时出错(Error adding an index to a view)

向视图添加索引时出错(Error adding an index to a view)

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

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（*）添加到选择中。

请帮我解决这个问题。

I have created a view using the following code

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`

Now when I use:

CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I ON two_weeks_performance (Date)

I am getting an error:

Cannot create index because its select list does not use the correct usage of COUNT_BIG(). Consider adding COUNT_BIG(*) to the select.

Please help me solve this issue.

最满意答案

该错误告诉您确切的操作 - 将COUNT_BIG（*）添加到您的选择列表中。

从创建索引视图 ：

如果指定了GROUP BY，则视图选择列表必须包含COUNT_BIG（*）表达式，并且视图定义不能指定HAVING，ROLLUP，CUBE或GROUPING SETS。

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)

The error tells you exactly what you have to do - add COUNT_BIG(*) to your select list.

From Creating Indexed Views:

If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.

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)