更新记录 #

• Update#1：@Schwern提供的解决方案非常出色！感谢您的分享！（如果SQL Server能自动执行这些操作就更好了，但目前我们已掌握处理方法）
• Update#2：原本抱有很大期望，但我无法在应用中成功使用ALTER TABLE ... SWITCH TO技巧！该技巧单次运行有效，但在存储过程中多次调用时会出现SQL Server错误，例如：

  Msg 4905, Level 16, State 1, Procedure procTest, Line 379 [Batch Start Line 0] ALTER TABLE SWITCH statement failed. The target table 'tempdb.dbo.#test' must be empty.

• Update#3：我正在测试是否可以通过CREATE SYNONYM或CREATE VIEW来隐藏临时表的替换操作

测试代码 #

SET STATISTICS TIME ON;

DROP TABLE IF EXISTS #test,#test1;

SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) id, 
    CAST(NULL as int) col1
INTO 
    #test
FROM 
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(a),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(a),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(a),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) D(a),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) E(a),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) F(a);

--SELECT COUNT(*),MIN(id),MAX(id) FROM #test;


PRINT '----- UPDATE T -----';
UPDATE T 
    SET col1=2*id
    FROM #test T WITH (TABLOCK);

PRINT '----- INTO #test1 -----';
SELECT id,2*id col1
    INTO #test1
    FROM #test T WITH (TABLOCK);

执行结果 #

----- UPDATE T -----
 SQL Server Execution Times:
   **CPU time = 12873 ms,  elapsed time = 4577 ms.**

(1000000 rows affected)

----- INTO #test1 -----

 SQL Server Execution Times:
   **CPU time = 2219 ms,  elapsed time = 892 ms.**

(1000000 rows affected)

可以看到SELECT ... INTO #temptable的速度约为UPDATE的5倍！请问是否有办法让UPDATE操作达到与SELECT INTO相同的速度？

解决方案解析 #

1. 性能差异的核心原因 #

SELECT INTO是直接批量写入新数据页，配合TABLOCK时仅持有表级锁，无需处理原有数据页的修改、页分裂，且日志写入优化程度极高；而UPDATE需要定位每行数据、修改原有页、维护索引、写入完整事务日志，这些额外开销直接导致速度差距。

2. 替代方案：用SELECT INTO逻辑实现类UPDATE效果 #

直接让UPDATE达到SELECT INTO的速度几乎不可能，但可通过以下方式实现同等性能的"更新"效果：

方案A：临时表替换+同义词封装

针对你测试的CREATE SYNONYM思路，可按以下流程实现：

1. 用SELECT INTO生成包含更新后数据的临时表
2. 删除原有同义词，创建指向新临时表的同义词
3. 业务逻辑通过同义词访问数据，实现透明替换

示例代码：

-- 生成更新后的临时表
SELECT id, 2*id AS col1 INTO #test_new FROM #test WITH (TABLOCK);

-- 替换同义词，隐藏临时表切换逻辑
DROP SYNONYM IF EXISTS dbo.test_syn;
CREATE SYNONYM dbo.test_syn FOR tempdb.dbo.#test_new;

此方案完全复用SELECT INTO的高性能，且无ALTER TABLE SWITCH的空表限制问题。

方案B：修复分区切换的报错问题

如果坚持使用ALTER TABLE SWITCH，需在每次调用前确保目标表为空，存储过程中可增加清空逻辑：

-- 每次切换前清空目标表
TRUNCATE TABLE #target_table;
ALTER TABLE #source_table SWITCH TO #target_table;

注意需保证源表和目标表结构、约束、索引完全一致。

3. 极端场景下的UPDATE性能优化 #

如果必须使用UPDATE，可通过以下手段缩小性能差距：

• 更新前禁用非聚集索引，更新完成后重建
• 在简单恢复模式下，配合TABLOCK和ALLOW_ROW_LOCKS = OFF开启最小日志记录
• 按分区拆分表，逐分区更新以减少锁范围和日志量

但即使做了这些优化，UPDATE速度仍难追平SELECT INTO，底层执行逻辑的差异无法消除。

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