SQL Server自连接性能优化：能否用窗口函数改写查询？

我需要在SQL Server中对一张近200万行的表执行复杂自连接操作，当前自连接耗时已超过1小时。请问是否可以使用窗口函数改写该查询？

示例数据 #

create table #Student(Code int, ExternalId varchar(20),FieldName varchar(100))

Insert into #Student(Code,ExternalId,FieldName)
values(100,'213-654','Address'),(100,'213-654','Address'),(100,'213-654','Address'),
(200,'675-238','Name'),(200,'675-238','Name'),
(300,'456-632','Gender'),(300,'456-632','Gender'),(300,'456-632','Gender'),
(400,'874-438',NULL),(400,'874-438',NULL)

当前查询语句 #

SELECT  St1.Code , St1.ExternalId  ,'FieldName' AS Discrepancy
FROM #Student St1    
  INNER JOIN #Student St2    
   ON St1.Code = St2.Code    
   AND St1.ExternalId = St2.ExternalId  
   where St1.FieldName <> St2.FieldName   
    AND St1.FieldName IS NOT NULL    
    AND St2.FieldName IS NOT NULL

完全可以用窗口函数或分组聚合改写，性能会比自连接提升几个量级。原自连接的核心问题是：当同一个(Code, ExternalId)组内有N行时，自连接会生成N*N行中间结果，200万行的表会导致中间数据量爆炸，这就是耗时超1小时的根本原因。

方法1：窗口函数写法 #

通过窗口函数在每个(Code, ExternalId)组内计算FieldName的最小值和最大值，若两者不等则说明组内存在差异值：

SELECT DISTINCT
    Code,
    ExternalId,
    'FieldName' AS Discrepancy
FROM (
    SELECT
        Code,
        ExternalId,
        FieldName,
        MIN(FieldName) OVER (PARTITION BY Code, ExternalId) AS MinField,
        MAX(FieldName) OVER (PARTITION BY Code, ExternalId) AS MaxField
    FROM #Student
    WHERE FieldName IS NOT NULL
) t
WHERE MinField <> MaxField

方法2：分组聚合写法（更高效） #

直接通过分组聚合完成统计，避免窗口函数的额外计算，性能更优：

SELECT
    Code,
    ExternalId,
    'FieldName' AS Discrepancy
FROM #Student
WHERE FieldName IS NOT NULL
GROUP BY Code, ExternalId
HAVING MIN(FieldName) <> MAX(FieldName)

性能优化建议 #

为了进一步提升查询速度，建议创建复合索引：

CREATE NONCLUSTERED INDEX IX_Student_Code_ExternalId_FieldName 
ON #Student(Code, ExternalId, FieldName)

该索引可以让SQL Server直接通过索引完成分组和统计，无需扫描全表。

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