在SQL Server中如何用Table类型变量优化小批量INSERT？-SQL-PHP中文网

普通table变量对insert无加速作用，因其无统计信息、无索引（除主键外）、优化器按1行估算致计划错误，且每次访问均全表扫描；真正有效的是memory_optimized表变量配合native_compilation存储过程使用。

在sql server中如何用table类型变量优化小批量insert？

直接用 table 变量做小批量 INSERT 不会提速，反而可能拖慢——它本质是内存中的一次性结构，没有索引、不参与查询优化器的统计信息推导，且每轮 INSERT ... SELECT @table_var 都触发隐式转换和行集扫描。真正有效的路径是：把 table 变量升级为内存优化表变量，并配合原生编译逻辑使用。

为什么普通 `table` 变量对 INSERT 没有加速作用

SQL Server 的 table 变量设计目标是“轻量临时容器”，不是性能载体：

它没有统计信息，优化器默认按 1 行估算，导致执行计划错配（比如该走哈希连接却选了嵌套循环）
无法建索引（除主键/唯一约束外），JOIN 或 WHERE 过滤时全表扫描
在非原生编译模块中访问时，仍走解释型执行路径，锁和日志开销未减少
插入后若需多次读取，每次都是新扫描，无缓存复用机制

必须用 `MEMORY_OPTIMIZED` 表变量 + `DURABILITY = SCHEMA_ONLY`

这才是小批量高频写入场景下能见效的组合：

先创建用户定义表类型：CREATE TYPE dbo.TVP_SmallBatch AS TABLE (id INT NOT NULL INDEX ix_id NONCLUSTERED, val NVARCHAR(50)) WITH (MEMORY_OPTIMIZED = ON);
声明时不能内联，必须两步：DECLARE @batch dbo.TVP_SmallBatch;
DURABILITY = SCHEMA_ONLY 是关键——数据纯内存驻留，无 checkpoint 文件 I/O，重启即清空，适合中间暂存
必须至少带一个索引（NONCLUSTERED 或 HASH），否则建表失败；HASH 索引需指定 BUCKET_COUNT，小批量建议设为 1024 起

INSERT 性能提升只发生在原生编译存储过程中

单独声明一个内存优化表变量，然后在普通 T-SQL 里 INSERT INTO @batch SELECT ...，几乎没收益。真正生效要满足三个硬条件：

整个操作必须包裹在 CREATE PROCEDURE ... WITH NATIVE_COMPILATION 内
所有涉及该表变量的操作（INSERT、SELECT、UPDATE）都必须在该过程体内完成
避免跨模块传递——虽然可作为 TVP 传入，但传入后在被调过程里仍走解释路径，失去原生优势

示例片段：

CREATE PROCEDURE usp_ProcessSmallBatch  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')  
  DECLARE @t dbo.TVP_SmallBatch;  
  INSERT INTO @t VALUES (1, N'foo'), (2, N'bar');  
  INSERT INTO dbo.RealTable SELECT * FROM @t;  
END

最容易被忽略的是：内存优化表变量的生命周期和作用域完全由声明它的原生编译模块控制；一旦过程退出，变量自动释放，不经过 tempdb，也不触发任何日志记录——这既是性能来源，也是调试盲区：你没法在过程外 SELECT 它来验证中间结果。