实现基于临时表的记录存在性校验及状态标记的高性能SQL

原代码问题分析 #

• 逻辑错误：IF EXISTS判断仅当存在匹配记录时插入VALID条目，但会因MainRecords中重复的CorrectNumber导致同一#InputRecords记录被重复插入；ELSE分支使用a.CorrectNumber <> t.CorrectNumber的JOIN会生成大量笛卡尔积，完全不符合“每条输入记录对应一条状态记录”的需求。
• 性能问题：多次扫描表，无效JOIN带来额外不必要的开销。

正确且高性能的实现方案 #

方案1：LEFT JOIN + CASE判断 #

仅需一次扫描两张表，通过去重MainRecords的CorrectNumber避免结果重复，保证每条输入记录仅生成一条结果：

CREATE TABLE #TempReferencIds
(
    InCorrectNumber varchar(20),
    CorrectNumber nvarchar(20),
    Status nvarchar(10),
)

INSERT INTO #TempReferencIds (InCorrectNumber, CorrectNumber, Status)
SELECT 
    t.InCorrectNumber,
    t.CorrectNumber,
    CASE WHEN m.CorrectNumber IS NOT NULL THEN 'VALID' ELSE 'INVALID' END AS Status
FROM #InputRecords t
LEFT JOIN (
    -- 提前去重，避免因主表重复数据导致结果重复
    SELECT DISTINCT CorrectNumber 
    FROM MainRecords
) m ON t.CorrectNumber = m.CorrectNumber

SELECT * FROM #TempReferencIds

方案2：EXISTS子查询（大场景下性能更优） #

EXISTS在找到匹配项后立即停止扫描，比JOIN更高效，尤其适合MainRecords数据量庞大的场景：

CREATE TABLE #TempReferencIds
(
    InCorrectNumber varchar(20),
    CorrectNumber nvarchar(20),
    Status nvarchar(10),
)

INSERT INTO #TempReferencIds (InCorrectNumber, CorrectNumber, Status)
SELECT 
    InCorrectNumber,
    CorrectNumber,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM MainRecords m 
        WHERE m.CorrectNumber = t.CorrectNumber
    ) THEN 'VALID' ELSE 'INVALID' END AS Status
FROM #InputRecords t

SELECT * FROM #TempReferencIds

性能优化建议 #

• 给MainRecords.CorrectNumber创建非聚集索引，大幅加速匹配速度：

  CREATE NONCLUSTERED INDEX IX_MainRecords_CorrectNumber ON MainRecords(CorrectNumber)
  

• 若#InputRecords数据量较大，可临时给其CorrectNumber字段创建索引，进一步提升匹配效率。
• 简化临时表结构，避免不必要的约束，减少写入开销。

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