SQL71501错误排查：Azure DevOps构建失败的物化视图问题

报错信息：Error SQL71501: 视图[PLTG].[MVW_SAURAV]存在对列[Process_Key]的未解析引用。

对应的物化视图创建SQL：

CREATE MATERIALIZED VIEW [PLTG].[MVW_SAURAV]
WITH 
( DISTRIBUTION =  HASH ([Process_Key]))
AS SELECT 
                      A.[PROCKEY] AS 'Process_Key',
                      A.[LOCATION] AS 'Location',
                      A.[INDICATOR] AS 'Indicator',
                      B.[SALESITEM] AS 'Sales Document item',
                      COUNT_BIG(*) AS 'TOTALCOUNT'

                      FROM [PLTG_STG].[ABC] A                                                                          
                    
                        INNER JOIN

             [PLTG_STG].[ZEN] B
            ON  A.[LOCATION] =  B.[LOCATION]
            GROUP BY 
            A.[PROCKEY],A.[LOCATION],A.[INDICATOR],B.[SALESITEM] ;

问题定位与修改方案 #

报错核心原因：物化视图的分布键定义不能使用SELECT子句中的列别名，必须引用底层查询的原始列名或GROUP BY子句中明确列出的列名。

你当前在WITH (DISTRIBUTION = HASH ([Process_Key]))里使用的Process_Key是A.[PROCKEY]的别名，Azure Synapse的构建验证逻辑无法识别该别名作为分布键，因此抛出未解析引用错误。

修改后的SQL：

CREATE MATERIALIZED VIEW [PLTG].[MVW_SAURAV]
WITH 
( DISTRIBUTION =  HASH ([PROCKEY])) -- 替换为原始列名PROCKEY
AS SELECT 
                      A.[PROCKEY] AS 'Process_Key',
                      A.[LOCATION] AS 'Location',
                      A.[INDICATOR] AS 'Indicator',
                      B.[SALESITEM] AS 'Sales Document item',
                      COUNT_BIG(*) AS 'TOTALCOUNT'

                      FROM [PLTG_STG].[ABC] A                                                                          
                    
                        INNER JOIN

             [PLTG_STG].[ZEN] B
            ON  A.[LOCATION] =  B.[LOCATION]
            GROUP BY 
            A.[PROCKEY],A.[LOCATION],A.[INDICATOR],B.[SALESITEM] ;

也可以写成HASH (A.[PROCKEY])，效果一致，都是明确指向原始表的列名。

本地验证通过但DevOps构建失败的原因 #

Visual Studio和SSMS的语法验证仅做基础SQL语法检查，未严格校验Azure Synapse物化视图的分布键规则；而Azure DevOps的构建流程会调用Synapse官方验证逻辑，对这类别名引用的限制检查更严格，因此出现本地通过但构建失败的差异。

内容的提问来源于stack exchange，提问作者saurav kumar Burnwal