正文

SQLSERVER 临时表和表变量的区别汇总（sqlserver怎么创建临时表）速看

9 分钟阅读

文章摘要

这篇文章介绍了SQL Server中的临时表（tempdb）和表变量（sys.dm_db_session_space_usage）之间的区别及其存储机制。文章指出，两者都存储在tempdb中，并通过mdf和ldf文件管理数据页。通过动态管理视图和sys.dm_db_session_space_usage等工具，验证了临时表和表变量确实会占用tempdb的存储空间。 文章进一步分析了临时表和表变量的主要区别：临时表具有统计信息，而表变量没有；临时表可以在后续进行修改，而表变量不能修改。此外，临时表的统计信息可以帮助SQL Server优化执行计划，而表变量由于缺乏统计信息，可能导致执行计划不准确。 最后，文章建议小白朋友优先使用临时表，因其操作简单且性能良好；而表变量适合需要批量操作且记录数不大的场景。但提醒大家，表变量的记录数如果偏离默认值，可能会导致性能问题，因此需要合理控制。


目录一：背景1. 讲故事二：到底有什么区别1. 前置思考2. 如何验证都存储在 tempdb 中 ?3. 不同点在哪里三：总结

今天和大家聊一套面试中经常被问到的高频题，对，就是 和 这俩玩意，如果有朋友在面试中回答的不好，可以尝试看下这篇能不能帮你成功迈过。

不管是 还是 都带了 这个词，既然提到了 ，按推理自然会落到某一个 中，如果真在一个 中，那自然就有它的存储文件 .mdf 和 .ldf，那是不是如我推理的那样呢？ 查阅 MSDN 的官方文档可以发现， 和 确实都会使用 tempdb 这个临时存储数据库，而且 tempdb 也有自己的 mdf，ndf，ldf 文件，截图如下：

有了这个大思想之后，接下来就可以进行验证了。

要想验证其实很简单，sqlserver 提供了多种方式观察。

查询的过程中观察 tempdb 下是否存在 表。使用动态管理视图 查询当前sql占用tempdb下的数据页个数。

为了让测试效果明显，我分别插入 10w 条记录观察 占用情况。

1.临时表插入 10w 条记录

CREATE TABLE #temp ( id INT, content CHAR(4000) DEFAULT ‘aaaaaaaaaa’ ); GO INSERT INTO #temp(id) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id FROM sys.objects AS o1,sys.objects AS o2; GO SELECT * FROM sys.dm_db_session_space_usage WHERE session_id=@@SPID;

从图中的 看，当前的 insert 操作占用了 50456 个数据页。

接下来展开 数据库以及观察到的 mdf 文件大小，都验证了存储到 tempdb 这个结论。

2.表变量插入 10w 条记录

因为表变量的特殊性，这里我故意暂停 让查询迟迟得不到结束，在这期间方便展开 tempdb，重启 sqlserver 恢复初始状态后，执行如下 sql：

DECLARE @temp TABLE ( id INT, content CHAR(4000) DEFAULT ‘aaaaaaaaaa’ ); INSERT INTO @temp(id) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id FROM sys.objects AS o1,sys.objects AS o2; SELECT * FROM sys.dm_db_session_space_usage WHERE session_id=@@SPID; WAITFOR DELAY ’00:01:00′

从图中可以看到 也会占用 5w+ 的数据页并且数据文件会膨胀。

对底层存储有了了解之后，接下来按照重要度从高到低来了解一下区别吧。

1.临时表有统计信息，而表变量没有

所谓的 ，就是对表数据绘制一个 来掌握数据的分布情况，sqlserver 在择取较优的执行计划时会严重依赖于这个 ，由于展开不了 列，这里就从执行计划上观察，如下图所示：

临时表下的执行计划

选中 之后点击 SSMS 的评估执行计划按钮来观察下评估执行计划，可以清晰的看到 sqlserver 知道表中有多少条记录，截图如下：

表变量下的执行计划

由于表变量的批处理性，我们用 把 xml 查询出来，然后点击观察可视化视图，参考sql 如下：

DECLARE @temp TABLE ( id INT, content CHAR(4000) DEFAULT ‘aaaaaaaaaa’ ); INSERT INTO @temp(id) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id FROM sys.objects AS o1,sys.objects AS o2; SET STATISTICS XML ON SELECT * FROM @temp WHERE id > 10 AND id<20; SET STATISTICS XML OFF

从图中可以清晰的看到，虽然表变量有 条记录，但由于没有统计信息，sqlserver 也就无法知道这张表的数据分布，所以就按照默认值 条来计算。

从这里大家也能看得出来，如果 的真实条数 和 默认的 1 严重偏移的话，会给 造成重大失误，这个大家一定要当心了。

2.其它使用上的区别

除了上一个本质上的不同，接下来就是一些使用上的不同了，比如：

临时表是 session 级的，表变量是 批处理 级

所谓的批处理，就是以 go 为界定，两者就是作用域上的不同。

临时表可以后续修改，表变量不能后续修改。

这里的修改涉及到 字段，索引，整体上来说临时表在使用上和普通表趋同，表变量不能进行后续修改。

总的来说， 没有统计信息，也不可以后续做 DDL 操作，这种情况下 比 更轻量级，不会有如下副作用：

DDL 修改导致执行计划过期重建sqlserver 对 统计信息 的维护压力

其实在这种作用域下高频的创建和删除表的操作中，表变量会让系统压力减轻很多。

但阳事总会有阴事来均衡它，一旦 的记录条数严重偏移默认的 ，会污染sqlserver的执行计划择取，可能会让你的 sql 遭受灭顶之灾，所以一定要控制 的记录条数，最好在百条内 。

最后的建议是：如果你是个小白可以无脑使用 ，90%的情况下都可以做到通杀，如果你是个高手可以考虑一下 。

到此这篇关于SQLSERVER 临时表和表变量到底有什么区别的文章就介绍到这了,更多相关SQLSERVER 临时表和表变量区别内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:sql server创建临时表的两种写法和删除临时表sqlserver通用的删除服务器上的所有相同后缀的临时表sql server 临时表 查找并删除的实现代码sql语句中临时表使用实例详解MySQL中临时表的基本创建与使用教程sql的临时表使用小结MySQL中关于临时表的一些基本使用方法SQL Server临时表的正确删除方式(sql server 删除临时表)