Visual Studio数据库项目物化视图SQL71640错误求助

问题场景 #

创建包含SUM聚合的物化视图时，出现矛盾的错误提示：

• 使用COUNT_BIG(*)时，触发错误：SQL71640: 创建包含SUM(a)的物化视图时，需要COUNT_BIG(a)
• 改为COUNT_BIG(1)后，错误提示变为需要COUNT_BIG(*)

示例脚本：

CREATE MATERIALIZED VIEW [EDW_DM].[MVW_Fact_Transaction] WITH ( DISTRIBUTION = HASH (H_PlayerHashKey)) AS 
SELECT
   some columns 
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x] * [Column x] * 0.0001, 0) AS MONEY)) AS Column x,
   COUNT_BIG(*) AS cb 
FROM
   [EDW_DM].[Fact_Table] 
GROUP BY
    some columns

错误提示翻译 #

SQL71640: 创建包含SUM(a)的物化视图时，需要搭配COUNT_BIG(a)使用

问题原因与解决方法 #

这是Azure Synapse物化视图的强制聚合一致性校验规则：当物化视图中使用SUM(ISNULL(列名, 0))这类带NULL值处理的SUM聚合时，必须为每个被聚合的列单独添加对应的COUNT_BIG(列名)（统计该列的非空行数），仅使用通用的COUNT_BIG(*)或COUNT_BIG(1)无法满足规则要求。

修正后的脚本示例：

CREATE MATERIALIZED VIEW [EDW_DM].[MVW_Fact_Transaction] WITH ( DISTRIBUTION = HASH (H_PlayerHashKey)) AS 
SELECT
   some columns,
   -- 第一个SUM聚合对应COUNT_BIG(Column1)
   SUM(CAST (ISNULL([Column1], 0) AS MONEY)) AS [Sum_Column1],
   COUNT_BIG([Column1]) AS [Count_Column1],
   -- 第二个SUM聚合对应COUNT_BIG(Column2)
   SUM(CAST (ISNULL([Column2], 0) AS MONEY)) AS [Sum_Column2],
   COUNT_BIG([Column2]) AS [Count_Column2],
   -- 第三个SUM聚合对应COUNT_BIG(Column3)
   SUM(CAST (ISNULL([Column3] * [Column3] * 0.0001, 0) AS MONEY)) AS [Sum_Column3],
   COUNT_BIG([Column3]) AS [Count_Column3],
   -- 可保留通用COUNT_BIG(*)，但必须先满足每个SUM对应COUNT_BIG(列名)
   COUNT_BIG(*) AS cb 
FROM
   [EDW_DM].[Fact_Table] 
GROUP BY
    some columns

规则的核心目的是让Synapse在增量刷新物化视图时，能够准确校验聚合数据的一致性，避免因NULL值处理导致的计算偏差。之前的错误是因为仅添加了通用计数，未为每个被SUM的列提供对应的非空计数，触发了规则校验失败。

内容的提问来源于stack exchange，提问作者user3268139