sql-server - 索引 View 中的 COUNT_BIG

Question

CREATE TABLE test2 (
id INTEGER,
name VARCHAR(10),
family VARCHAR(10),
amount INTEGER)

CREATE VIEW dbo.test2_v WITH SCHEMABINDING 
AS
SELECT id, SUM(amount) as amount
-- , COUNT_BIG(*) as tmp
FROM dbo.test2 
GROUP BY id

CREATE UNIQUE CLUSTERED INDEX vIdx ON test2_v(id)

我的代码有错误:

Cannot create index on view 'test.dbo.test2_v' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

我可以创建这样的 View :

CREATE VIEW dbo.test2_v WITH SCHEMABINDING 
    AS
    SELECT id, SUM(amount) as amount, COUNT_BIG(*) as tmp
    FROM dbo.test2 
    GROUP BY id

但我只是想知道在这种情况下本专栏的目的是什么？

Answer 1

在这种情况下，您需要 COUNT_BIG，因为您正在使用 GROUP BY。

这是索引 View 的众多限制之一，由于这些限制，索引 View 无法在许多地方使用，或者它的使用没有达到应有的效果。不幸的是，这就是目前的运作方式。糟糕，它缩小了使用范围。

http://technet.microsoft.com/en-us/library/cc917715.aspx