临时表和表变量到底怎么选，别一刀切，看情况灵活用才对

说到在SQL Server里写查询，临时表和表变量是两个经常被拿出来比较的工具，很多人喜欢问“到底哪个更好？”然后希望得到一个简单的答案，永远用表变量”或者“临时表性能更好”，但实际情况是，这种一刀切的答案往往是错的，选择哪个，真的得像看天气决定穿什么衣服一样，得看具体情况。

（来源：基于SQL Server社区广泛的技术讨论与实践经验，如Brent Ozar、Paul White等专家的观点）

先简单认识一下它俩

你可以把表变量想象成一个一次性的小篮子,你在一个批处理语句或者存储过程里声明它，往里放点数据，用完了它就自动消失了，它的生命周期很短暂，只存在于当前会话的这次“对话”中，创建它很简单，用DECLARE @TableName TABLE这种语法就行。

临时表则更像一个临时搭建的工棚,它也是在当前会话中创建，但它的存在感更强，会实实在在地被创建在系统的临时数据库（tempdb）里，直到你主动删除它（DROP TABLE），或者你的会话结束，它才会消失，创建它用的是CREATE TABLE #TableName。

关键区别在哪里？看这几点来做选择

1. 数据量是首要考虑因素 这是最核心的一条经验法则，表变量天生被设计用来处理小规模的数据集，这个“小”没有绝对标准，但通常认为几百行到几千行以内是比较安全的范围，一旦你的数据量超过了这个范围，比如达到几万、几十万行，表变量的性能可能会急剧下降。 为什么呢？因为SQL Server在创建表变量时，默认认为它里面没多少数据，所以不会为它创建分布统计信息，统计信息是查询优化器决定“怎么查最快”的关键依据，没有这个，优化器就像蒙着眼睛开车，很容易选错执行计划，导致查询慢得离谱。 而临时表则不同，它会像正式表一样拥有统计信息，当数据量很大，或者查询条件很复杂（比如有JOIN、WHERE子句）时，优化器能根据统计信息做出聪明的决策。数据量大、查询复杂，优先考虑临时表。（来源：Microsoft Docs，关于临时对象与统计信息的说明）

   

2. 你需要事务支持吗？ 这是一个容易被忽略但很重要的点，表变量基本上不参与“大”事务，什么意思？如果你在一個包含了表变量操作的语句块外面开启了显式事务（BEGIN TRANSACTION），那么回滚（ROLLBACK）这个事务时，对表变量所做的修改是不会被回滚的，它只遵循自己隐式的事务生命周期。 临时表则完全参与到事务中，事务回滚时，对临时表的所有修改也会一起回滚，保持数据的一致性。 如果你的临时数据处理逻辑是某个重要业务事务的一部分，需要保证“同生共死”，那么必须使用临时表，如果只是中间暂存，不在乎部分修改是否撤销，表变量可能更简单。

3. 索引能帮你提速吗？ 表变量在声明时只能创建主键或唯一约束（这本身会创建索引），但不能在声明后直接使用CREATE INDEX语句为它创建非聚集索引，虽然你可以通过添加约束的方式来变通实现，但非常不灵活。 临时表则完全没有这个限制，你可以在创建表后，根据需要为它创建多个、多种类型的索引，这对于优化复杂查询的性能至关重要。 如果你的中间结果集需要依赖非聚集索引来加速查找、排序，临时表是唯一的选择。

4. 存储过程之间需要共享数据吗？ 表变量的作用域（可见范围）非常狭窄，它只在声明它的批处理、存储过程或函数内有效，你不能在存储过程A里创建一个表变量，然后传给存储过程B去使用。 临时表的作用域则宽泛一些，它在当前会话中都是可见的，这意味着，你可以在存储过程A里创建临时表，然后在同一个会话中调用的存储过程B里，可以直接对这个临时表进行查询或修改，这在需要分步处理、传递中间结果的场景下非常有用。 需要跨存储过程共享数据，只能用临时表。

   

怎么灵活选？

• 数据量小，逻辑简单，就是个临时容器。 你只是需要暂存几十行ID，然后去主表里查询详细信息，这种情况下，表变量是首选，它语法简洁，自动清理，资源开销小，非常轻便。

• 数据量大，查询复杂，需要索引和统计信息支持。 你需要从一个上百万行的表里筛选出几万行数据，进行多表连接、分组聚合等复杂操作，这时候，临时表的优势就体现出来了，虽然创建它比表变量稍慢一点，但后续复杂的查询性能会好得多。

• 操作是原子事务的一部分，需要回滚保证一致性。 你的数据处理步骤和更新正式表的操作在同一个事务里，必须确保所有步骤一起成功或失败。临时表是唯一正确的选择。

• 存储过程的“流水线”作业。 如果你的设计是多个存储过程像流水线一样协作处理数据，中间结果需要传递，那么临时表是连接这些过程的桥梁。

记住没有银弹,最好的办法就是带着这些原则，在实际环境中对两种方法都进行测试（Profiler或扩展事件），用真实的执行计划和执行时间来说话，通过实践，你就能培养出针对不同场景做出最佳选择的直觉。