SQL Server 知识梳理:临时表与表变量
此为系列原创文章,为本人在项目及学习中对SQL Server的知识梳理。由于本人水平有限,疏漏甚至错误难以避免。希望各位如有发现不当之处,不吝指出
作为一个开发人员,免不了在实际的项目开发过程中需要用用到临时表与表变量。其作为SQL Server 定义的内置对象,必定有其存在的道理。有时,我们需要使用临时表来完成一个任务,而有时,或许表变量会是合适的选择。
那么,什么时候需要用临时表,而什么时候需要表变量呢?它们之间有什么差异?
临时表与表变量与标准的用户自定义表的作用并不相同,由于其临时性,这些对象变得很有用且高效,它们就如同在对象之间传递数据的工具,也就是为了完成临时工具而创建的短期暂存表。
临时表
SQL Server 定义了两种类型的临时表,本地临时表与全局临时表。
本地临时表
临时表与标准表的创建方法是相同的,都是通过DDL来创建的。其创建语法来说,唯一的区别在于表名的差异。创建本地临时表时,表名前面必须有一个 # 符号。而创建全局临时表时,表名必须以 ##开头。创建临时表时,其将被存储在tempdb的磁盘空间中。
CREATE TABLE #ProductTemp( ProductID INT PRIMARY KEY );
临时表与标准表不同之处也在于其生命周期。当创建它的批处理,存储过程结束时,就会删除临时表。如果是在交互式会话中(Query Editor)中创建临时表,在会话结束后,也会删除临时表。当然你也可以像删除标准表那样手动删除临时表。
除此之外,本地临时表与标准表的相同之处在于,本地临时表也会具有标准表的许多特性,比如主键,外键,聚集索引,非聚集索引。
本地临时表作为实际开发中常用的一种临时表,其作用域限制在创建此本地临时表的连接中。如何理解呢?举个简单的例子,你用两个客户端,打开两个连接,即使你使用相同的名称 #ProductTemp 创建了临时表,那么这两个临时表也是作为不同的对象存在于tempdb中的。SQL Server 会使用连接标识符与表名来拼接为各个本地临时表的唯一标识。
全局临时表
全局临时表与本地临时表相似。最主要的区别在于以下两点:
- 作用域不同。本地临时表只有在创建其的本地连接内才可以看到。而全局临时表的作用域会延续到访问表的最后一个连接。
- 命名规则不同,本地临时表以一个# 开头,而全局临时表以 ## 开头。
需要考虑的是,当我们为了完成某一功能而创建全局临时表时,可以考虑下创建标准表是否是更好的选择。
替代方案
临时表的另一种替代方案是在tempdb中创建标准用户表。每次SQL Server重新启动时,都会重新构建tempdb。这客观上可以作为临时表的一个代替方案。
表变量
表变量与临时表其结构是类似的,然而还是有一些不同之处。
- 首先,创建语法不同。究其根本来说,表变量是一个变量,因而符合变量的命名及创建语法。
- 其次,表变量作为一个局部变量,其作用域和生命周期与一般的局部变量一致。只有创建表变量的批处理,过程及函数才能看到此表变量。而且表变量也可以作为参数传递,当作为参数传递时,其是只读的。
- 表变量只可以有主键,而不能有外键。
- 表变量必须单独存在,而不能有外键依赖或者触发器。
DECLARE @tb1 Table ( Id int, Name varchar(20), Age int ) INSERT INTO @tb1 VALUES(1,'张三',20) SELECT * FROM @tb1
最佳实践
以上可以看到,临时表和表变量是有很多不同之处的。那么我们在实际工作中要如何选择呢?一个经验法则是:如果存储的数据多于250行,则倾向于使用临时表,否则,请考虑使用表变量。具体原因在于临时表是有统计计数信息的,而表变量却没有。如果存储的临时数据比较多,那么统计计数可用来优化查询计数。那么势必选择临时表是首先采取的方案。