SQL Server中的临时表和表变量
在SQL Server的性能调优中,有一个不可比面的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQL Server应用性能评估和调优的时候就看到过大量的临时数据集处理需求,而他们的开发人员就无法确定什么时候用临时表,什么时候用表变量,因此他们就简单的使用了临时表。实际上临时表和表变量都有特定的适用环境。
先卖弄一些基础的知识:
表变量
变量都以@或@@为前缀,表变量是变量的一种,另外一种变量被称为标量(可以理解为标准变量,就是标准数据类型的变量,例如整型int或者日期型DateTime)。以@前缀的表变量是本地的,因此只有在当前用户会话中才可以访问,而@@前缀的表变量是全局的,通常都是系统变量,比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量,因此它只能在一个Batch中生存,也就是我们所说的边界,超出了这个边界,表变量也就消亡了。
表变量存放在内存中,正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题,因此也不需要锁机制,对于非常繁忙的系统来说,避免锁的使用可以减少一部分系统负载。
表变量另外还有一个限制就是不能创建索引,当然也不存在统计数据的问题,因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了),这一特性有的时候是件好事,而有些时候却会造成一些麻烦。
临时表
临时对象都以#或##为前缀,临时表是临时对象的一种,还有例如临时存储过程、临时函数之类的临时对象,临时对象都存储在tempdb中。以#前缀的临时表为本地的,因此只有在当前用户会话中才可以访问,而##前缀的临时表是全局的,因此所有用户会话都可以访问。临时表以会话为边界,只要创建临时表的会话没有结束,临时表就会持续存在,当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。
我们前面说过临时表存储在tempdb中,因此临时表的访问是有可能造成物理IO的,当然在修改时也需要生成日志来确保一致性,同时锁机制也是不可缺少的。
跟表变量另外一个显著去别就是临时表可以创建索引,也可以定义统计数据,因此SQL Server在处理访问临时表的语句时需要考虑执行计划优化的问题。
表变量 vs. 临时表
结论
综上所述,大家会发现临时表和表变量在底层处理机制上是有很多差别的。
简单地总结,我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大,如果在代码中用于临时计算,同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化,比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等),也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力,如果代码的运行实例很多,就要特别注意内存变量对内存的消耗。
一般对于大的数据集我们推荐使用临时表,同时创建索引,或者通过SQL Server的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据,当然临时表就是唯一的选择了。需要提及的是,由于临时表存放在tempdb中,因此要注意tempdb的调优。
SQL中的临时表和表变量
我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.
(1) 临时表 局部临时表 全局临时表
(2)
表变量
临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用.
我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享.
局部临时表
局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如:
CREATE TABLE [#DimCustomer_test]
(
[CustomerKey] [int] ,
[FirstName] [nvarchar](50) ,
[MiddleName] [nvarchar](50) ,
[LastName] [nvarchar](50)
)
|
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name
LIKE ’%DimCustomer%’
|
the Result is:
name
#DimCustomer_test__________________________________000000000005
|
全局临时表
下面我们来看一下全局临时表:
CREATE TABLE [##DimCustomer_test]
(
[CustomerKey] [int],
[FirstName]
[nvarchar](50),
[MiddleName] [nvarchar](50),
[LastName] [nvarchar](50)
)
|
现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE
’%DimCustomer%’
|
The Result are:
name
#DimCustomer_test______________________________000000000005
##DimCustomer_test
|
DROP TABLE [##DimCustomer_test]
DROP TABLE [#DimCustomer_test]
可以看到我们刚才创建的全局临时表名字并没有被加上标识.
表变量
表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些!
另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.
表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
相关推荐
资源名称:SQLServer中临时表与表变量的区别内容简介: 本文档主要讲述的是SQLServer中临时表与表变量的区别;希望本文档会给有需要的朋友带来帮助;感兴趣的朋友可以过来看看。资源截图: 资源太大,传百度网盘了,...
在SQL Server中,临时表和表变量是两种用于暂存数据的结构,它们在数据库操作中扮演着重要的角色。理解它们的区别有助于优化存储过程和提高性能。 临时表类似于永久表,但它们存在于Tempdb系统数据库中,仅在创建它...
### SQL Server 中临时表与表变量的区别 #### 一、临时表 临时表与常规的永久表非常相似,主要区别在于其创建位置和生命周期。在SQL Server中,临时表是在`tempdb`数据库中创建的,这意味着它们仅在这个特定会话...
- 局部临时表的生命周期与创建它的批处理或存储过程紧密相关。 - 全局临时表在创建后,只有当最后引用它的会话结束时才会被删除。 4. **共享**: - 局部临时表不共享,而全局临时表在创建后可供多个会话使用,但...
**2.3.3 表变量与临时表的选择** 表变量相比于临时表,可以有效减少存储过程的重新编译次数。表变量具有更短的作用域,且在事务回滚时不会受到影响。然而,表变量不支持索引构建,因此对于数据量较大的情况,使用带...
8. **解析与引用**:如果同时存在同名的本地和全局临时表,查询可能无法明确解析到哪个表,除非使用完全限定名(包括井字号)来指定。 临时表在处理一次性数据需求、复杂查询的中间结果存储以及避免多次计算重复...
#### 一、临时表与磁盘临时表 在MySQL数据库管理系统中,**临时表**是一种特殊类型的表,主要用于存储中间结果集,它可以帮助执行一些复杂的查询操作,如`GROUP BY`、`DISTINCT`、某些`ORDER BY`以及`UNION`等操作...
文章还提到了临时表与表变量的对比。临时表在使用时需要写入磁盘,因此在执行效率上相对较低,尤其是在处理大量数据时。与之相比,表变量不涉及磁盘I/O操作,因此在处理普通数据量的情况下,使用表变量更具有优势。 ...
#### 临时表与内存存储 为了进一步提高性能,可以将临时表设置为HEAP类型,即在内存中存储数据。HEAP表(在MySQL中已替换为MEMORY引擎)通过在RAM中存储数据而非磁盘,显著提升了读写速度。创建内存存储的临时表...
6. **环境变量**:VFP的环境变量如USER, WORKAREA等可能没有正确设置,这可能影响到临时表的使用。 7. **数据库连接**:如果连编的应用程序改变了数据库连接方式,比如使用了不同的数据库引擎或连接字符串,可能...
4. **临时表与表变量的比较** - **表变量**:以@或@@前缀,@是局部的,@@是全局的系统变量。表变量在同一个批处理中有效,且不支持索引,因此在大量数据处理时可能性能较低。 - **临时表**:支持索引,适用于大...
4. **表变量与临时表**: - **表变量**:一种用于存储单个记录集的方法。 - **临时表**:用于存储中间结果,不会影响生产数据。 5. **事务处理**: - **事务管理**:掌握事务的基本概念和使用方法,包括提交、...
9. **表变量与临时表**:这两种临时存储结构在存储引擎中有特定的处理方式,它们在存储和性能上有各自的特点。 10. **性能监视与调优**:通过动态管理视图(DMV)、性能计数器和SQL Server Profiler,可以监控系统...
临时表是数据库中的特殊表格,它的生命周期只存在于会话(Session)内,一旦会话结束,临时表及其数据都会被自动删除。这使得它们非常适合用于存储过程中的临时数据存储,不会对其他用户或会话产生影响。 创建临时...
临时表与表变量的比较: 表变量以 `@` 或 `@@` 开头,其中 `@` 代表本地变量,`@@` 代表全局系统变量。表变量在内存中存储,执行速度快,但不记录日志,因此适用于小规模的数据集。而临时表则存储在 `tempdb` 数据库...
### 一个选查询后插入到一个临时表的Oracle函数 #### 概述 在Oracle数据库环境中,函数是一种非常有用的数据库对象,它允许开发者封装复杂的逻辑并返回特定的结果。本文将详细解析一个特定的Oracle函数——`NS_...
sqlserver 循环临时表插入数据到另一张表 -- 声明变量 DECLARE @SupCode as varchar(100), @ProdCode as varchar(50), @PackLayer as varchar(50), @CodeStatus as varchar(50), @ProductId as varchar(50), @...