sql server 表变量和临时表

原创 2011年06月21日 10:34:00

临时表与永久表相似,但临时表存储在 tempdb 中,当不再使用时会自动删除。

临时表有两种类型:本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。

例如,如果创建了 employees 表,则任何在数据库中有使用该表的安全权限的用户都可以使用该表,除非已将其删除。如果数据库会话创建了本地临时表 #employees,则仅会话可以使用该表,会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表,则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用,则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该 表,则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

临时表

临时表存储在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:

#DimCustomer_test___________________________________________________________________________________________________000000000005

##DimCustomer_test

 

--Drop test temp tables

                               DROP TABLE [##DimCustomer_test]

                               DROP TABLE [#DimCustomer_test]

 

可以看到我们刚才创建的全局临时表名字并没有被加上标识.

 

表变量

表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些!

 

另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如:

 

  USE AdventureWorksDW

   GO

 

   DECLARE @DimCustomer_test TABLE

   (

      [CustomerKey] [int]

      ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

      )

   ---insert data to @DimCustomer_test

   INSERT @DimCustomer_test

   (

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

      )

   SELECT  

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

   FROM DimCustomer

 

   SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)

FROM @DimCustomer_test   INNER JOIN FactInternetSales    ON

@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey

Group BY CustomerKey

 

Result:

 

Server: Msg 137, Level 15, State 2, Line 32

Must declare the variable ’@DimCustomer_test’.

 

 

如果我们对上面的查询进行更改,对查询使用别名(并且找开IO):

-----in the follow script,we used the table alias.

 

DECLARE @DimCustomer_test TABLE

(

     [CustomerKey] [int]

     ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

     )

 

INSERT @DimCustomer_test

(

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

     )

SELECT  

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

FROM DimCustomer

 

SELECT t.CustomerKey,f.OrderQuantity

FROM @DimCustomer_test t INNER JOIN FactInternetSales   f ON

t.CustomerKey = f.CustomerKey

where t.CustomerKey=13513

 

表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.
表变量主要开销系统的内存,而临时表则使用tempdb。对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据量很庞大时,建议使用临时表。具体使用表变量还是临时表,可以根据系统的运行状况来调整。

====================================================

例如,如果创建名为   employees   的表,则任何人只要在数据库中有使用该表的安全权限就可以使用该表,除非它已删除。如果创建名为   #employees   的本地临时表,只有您能对该表执行操作且在断开连接时该表删除。如果创建名为   ##employees   的全局临时表,数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用,则当您断开连接时该表删除。如果该表在您创建后有其他用户使 用,则   SQL   Server在所有用户断开连接后删除该表。

=====================================================

非索引视图只是一个定义, 不存储数据, 查询的时候才从基础表拿数据

索引视图会存储数据

 

索引视图和临时表的数据都存储在硬盘

其中索引视图的数据存储在视图所在的数据库文件中

临时表的数据存储在tempdb这个数据库文件中

 

问题 1:为什么在已经有了临时表的情况下还要引入表变量?

解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明确定义的范围,在该范围结束时会自动清除这些表变量。
• 与临时表相比,表变量导致存储过程的重新编译更少。
• 涉及表变量的事务仅维持表变量上更新的持续时间。因此,使用表变量时,需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分,所以事务回滚并不影响它们。
问题 2:如果说使用表变量比使用临时表导致存储过程的重新编译更少,这意味着什么?

解答 2:下面的文章讨论了重新编译存储过程的一些原因:


243586 (http://support.microsoft.com/kb/243586/) 存储过程重新编译的疑难解答
“由于某些临时表操作引起的重新编译”一节还列出了为避免一些问题(例如使用临时表导致重新编译)而需要满足的一些要求。这些限制不适用于表变量。

表变量完全独立于创建这些表变量的批,因此,当执行 CREATE 或 ALTER 语句时,不会发生“重新解析”,而在使用临时表时可能会发生“重新解析”。临时表需要此“重新解析”,以便从嵌套存储过程引用该表。表变量完全避免了此问题,因此存储过程可以使用已编译的计划,从而节省了处理存储过程的资源。

问题 3:表变量有哪些缺陷?

解答 3:与临时表相比,它存在下列缺陷: • 在表变量上不能创建非聚集索引(为 PRIMARY 或 UNIQUE 约束创建的系统索引除外)。与具有非聚集索引的临时表相比,这可能会影响查询性能。
• 表变量不像临时表那样可以维护统计信息。在表变量上,不能通过自动创建或使用 CREATE STATISTICS 语句来创建统计信息。因此,在大表上进行复杂查询时,缺少统计信息可能会妨碍优化器确定查询的最佳计划,从而影响该查询的性能。
• 在初始 DECLARE 语句后不能更改表定义。
• 表变量不能在 INSERT EXEC 或 SELECT INTO 语句中使用。
• 表类型声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。
• 如果表变量是在 EXEC 语句或 sp_executesql 存储过程外创建的,则不能使用 EXEC 语句或 sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用,因此 EXEC 语句和 sp_executesql 存储过程将在表变量的作用域之外。但是,您可以在 EXEC 语句或 sp_executesql 存储过程内创建表变量并执行所有处理,因为这样表变量本地作用域将位于 EXEC 语句或 sp_executesql 存储过程中。
问题 4:与临时表或永久表相比,表变量的仅存在于内存中的结构保证了更好的性能,是否因为它们是在驻留在物理磁盘上的数据库中维护的?

解答 4:表变量不是仅存在于内存中的结构。由于表变量可能保留的数据较多,内存中容纳不下,因此它必须在磁盘上有一个位置来存储数据。与临时表类似,表变量是在 tempdb 数据库中创建的。如果有足够的内存,则表变量和临时表都在内存(数据缓存)中创建和处理。

问题 5:必须使用表变量来代替临时表吗?

解答 5:答案取决于以下三个因素: • 插入到表中的行数。
• 从中保存查询的重新编译的次数。
• 查询类型及其对性能的指数和统计信息的依赖性。
在某些情况下,可将一个具有临时表的存储过程拆分为多个较小的存储过程,以便在较小的单元上进行重新编译。

通常情况下,应尽量使用表变量,除非数据量非常大并且需要重复使用表。在这种情况下,可以在临时表上创建索引以提高查询性能。但是,各种方案可能互不相同。Microsoft 建议您做一个测试,来验证表变量对于特定的查询或存储过程是否比临时表更有效。

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明...

SQL Server中的临时表和表变量

在SQLServer的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQLServ...

SQL Server中的临时表和表变量 Declare @Tablename Table

[转自]http://zhengweisincere.blog.163.com/blog/static/498446492009625749522/ 在SQL Server的性能调优中,有一...

【转】SQL Server 表变量和临时表的区别

SQL Server 表变量和临时表的区别

SQL Server 表变量和临时表的区别

一、表变量   表变量在SQL Server 2000中首次被引入。表变量的具体定义包括列定义,列名,数据类型和约束。而在表变量中可以使用的约束包括主键约束,唯一约束,NULL约束和CHEC...

SQL Server临时表和表变量

文章主要描述的是SQL Server临时表和表变量在实际操作中的区别介绍,我们在数据库中实际操作中,对于表的使用的时候,一般都会使用两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们...

SQL Server中 临时表 与 表变量的区别 1

什么情况下使用表变量?什么情况下使用临时表?表变量:    DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))    I...

SQL Server中临时表与表变量的区别

我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候...

SQL SERVER 中临时表与表变量的区别

我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候...

SQL Server中的临时表和表变量的区别

在SQL Server的性能调优中,有一个不可比面的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQL Se...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql server 表变量和临时表
举报原因:
原因补充:

(最多只允许输入30个字)