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

最新推荐文章于 2023-03-16 00:14:57 发布
最新推荐文章于 2023-03-16 00:14:57 发布
阅读量6.4k 收藏 4
点赞数 1
分类专栏： SQL/数据库 文章标签： sql server table 存储 insert 优化 数据库

[转自]http://zhengweisincere.blog.163.com/blog/static/498446492009625749522/

在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中的临时表和表变量


我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.

临时表

局部临时表

全局临时表

表变量

                              

临时表

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

表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.


----------------------------------------

另外在今天帮同事Tuning SQL 脚本地时候，发现对于大数据量表的查询（10w-100W），用变量的方式比用select 的方式居然执行时间减少了100倍！！似懂非懂，但从来没有想到差别如此大，惊讶ing，记录一笔，研究一下

M1：

declare @tempID int

set @tempID =(select lots_id from qs_notes where id='CVT20080321')

select * from ls_Qs_notes where id = @tempID

---返回记录998，行执行时间6589

M2：

select * from ls_Qs_notes where id =(select lots_id from qs_notes where id='CVT20080321')

---返回记录998 ，行执行时间60


苦苦的潜行者
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL Server 表变量和临时表的区别(详细补充篇)
12-15
　表变量在SQL Server 2000中首次被引入。表变量的具体定义包括列定义，列名，数据类型和约束。而在表变量中可以使用的约束包括主键约束，唯一约束，NULL约束和CHECK约束(外键约束不能在表变量中使用)。定义表变量的...
sql server 2008 中declare的 @用法_基本技能篇：SQL 的变量
weixin_39846089的博客
12-17 8421
上一篇讲到MSSQL的数据类型，今天简单说说变量的知识。变量，是相对于常量而言的一个概念，字面的意思常量就是一个不变的值，如1,2，3,4,5,6...a,bc之类的，变量就是一个可变的值。变量可分为局部变量和全局变量，局部变量需要预先定义,前面以一个@开头，常于declare联用，全局变量可以看作是系统变量，不用定义可以直接调用，前面以两个@开头。以下展开来说一下：(1)局部变量：定义...
DECLARE在SQL中的用法及相关等等
小吉霸熊的专栏
11-22 4125
:arrow: 允许用户创建游标， 用于在一个大的查询里面检索少数几行数据。 变量是在批处理或过程的主体中用 DECLARE 语句声明的，并用 SET 或 SELECT 语句赋值。游标变量可使用此语句声明，并可用于其他与游标相关的语句。除非在声明中提供值，否则声明之后所有变量将初始化为 NULL。 Transact-SQL 语法约定 语法 DECLARE { ...
轉：SQL Server中的临时表和表变量
weixin_30741653的博客
08-05 304
轉：SQL Server中的临时表和表变量 2008-04-07 15:59 在SQL Server的性能调优中，有一个不可比面的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集？表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQL Server应用性能评估和调优的时候就看到过大量的临时...
SQL临时表和表变量 Declare @Tablename Table
Cien
08-30 7137
在SQL Server的性能调优中，有一个不可比面的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集？表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQL Server应用性能评估和调优的时候就看到过大量的临时数据集处理需求，而他们的开发人员就无法确定什么时候用临时表，什么时候用表变量，因此他们就简单的使用了临时表。实际上临时表和表变量都有特定的适用环境。
南大通用GBase8s 常用SQL语句（九十二）
weixin_64708585的博客
03-21 1165
CREATE TEMP TABLE 语句 使用 CREATE TEMP TABLE 语句在当前数据库中创建临时表。 语法 元素 描述 限制 语法 table 声明表的名称 在会话中必须是唯一的。请参阅 命名临时表 标识符 用法 您必须具有数据库上的 Connect 特权才能创建临时表。该临时表只对创建它的...
PostgreSQL temp table 全链路 实现原理
天行健，地势坤
03-16 2167
PG为了方便对整个数据库内部的表进行管理，设计了namespace，将系统表、用户表、临时表等不同schema 的表划分到不同的namespace中，对于这一些表的owner 以及 它们的权限控制就都可以转移对该表所属的 namespace的权限控制了。（大宽表）的存储是默认开启的，比如当前tuple的大小超过 page 1/2，会默认开启toast的存储，将实际的 tuple数据部分存储到 toast表中，原本的heap表的tupe data部分则保存指向 toast表的指针；
关于Sqlserver动态语句传递表变量的用法
morliz的博客
12-29 756
随手笔记：动态Sql语句传递表变量
sqlserver 临时表的用法
12-15
用法： 用于复杂查询时可以用临时表来暂存相关记录，能够提高效率、提高程序的可读性，类似于游标中的 my_cursor declare my_cursor cursor scroll for select 字段 from tablename 临时表分为：用户临时表和系统...
sqlserver 动态创建临时表的语句分享
12-15
原先Insus.NET使用下面代码实现： 代码如下: DECLARE @s NVARCHAR(MAX) = ‘ IF OBJECT_ID(”[dbo].[#Tb]”) IS NOT NULL DROP TABLE [dbo].[#Tb] CREATE TABLE [dbo].[#Tb] ( [xxx] INT, [xxx] NVARCHAR(50), ‘+ ...
sqlserver通用的删除服务器上的所有相同后缀的临时表
12-15
代码如下: use tempdb if object_id(‘tempdb..#table’) is not null drop table tempdb..#table ...select’ declare @table varchar(100),@count int select @count=count(name) from tempdb..#table while(@count>0
SQLserver中的declare变量用法
12-14
平时写SQL查询、存储过程都是凭着感觉来，没有探究过SQL的具体语法，一直都是按c#那一套往SQL上模仿，前几天项目中碰到一个问题引起了我对declare定义变量的作用域的兴趣。 大家都知道c#中的局部变量，在if中如果...
SQL Server遍历表中记录的2种方法(使用表变量和游标)
12-15
本文将介绍利用使用表变量和游标实现数据库中表的遍历。 表变量来实现表的遍历 以下代码中，代码块之间的差异已经用灰色的背景标记。 代码如下: DECLARE @temp TABLE ( [id] INT IDENTITY(1, 1) , [Name] VARCHAR(10...
sqlserver 循环临时表插入数据到另一张表
11-27
sqlserver 循环临时表插入数据到另一张表 -- 声明变量 DECLARE @SupCode as varchar(100), @ProdCode as varchar(50), @PackLayer as varchar(50), @CodeStatus as varchar(50), @ProductId as varchar(50), @...
Sqlserver 表类型和表变量介绍
12-15
表类型可以用在存储过程中，用于批量增加表类型定义： 代码如下:CREATE TYPE dbo.SubCardTable as table ( [SC_ID] [varchar](50), [ZhuKaInfo_ID] [varchar](50), [Project_KeyName] [varchar](50), [SC_Card...
SqlServer中用exec处理sql字符串中含有变量的小例子
12-15
SqlServer中，用exec处理sql字符串中含有变量，提示：必须声明标量变量@（已解决！） 代码如下:declare @tssj nvarchar(100) — 外部变量declare @Sql_Sql nvarchar(100) — 存sql字符串变量 set @Sql_Sql =N’...
Sql Server里删除数据表中重复记录的例子
01-21
1、生成一张临时表new_users，表结构与users表一样； 2、对users表按id做一个循环，每从users表中读出一个条记录，判断new_users中是否存在有相同的u_name，如果没有，则把它插入新表；如果已经有了相同的项，则忽略...
SQL临时表递归查询子信息并返回记录的代码
12-15
declare @QAExp_ID char(36) –指标属性公式ID set @QAExp_ID=’3D2B8F3F-0B7E-46FD-9B33-050F846C2869′ declare @temp_qid table(QID char(36),ExpValue nvarchar(max)) –临时表变量获得指标根ID declare @...
sqlserver 局部变量的使用
12-15
A. 使用 DECLARE 下例使用名为 @find 的局部变量检索所有姓以 Ring 开头... 在 DECLARE 中使用两个变量 下例从 Binnet & Hardley (pub_id = 0877) 的雇员中检索从 1993 年 1 月 1 日起所雇佣的雇员名称。 代码如下: USE
sqlserver 遍历临时表
最新发布
01-28
在SQL Server中，可以使用游标（cursor）来遍历临时表。 首先，我们需要创建一个临时表，并向其插入数据。可以使用以下语句来创建和插入数据： ```sql CREATE TABLE #TempTable ( ID INT, Name VARCHAR(50) ) INSERT INTO #TempTable(ID, Name) VALUES (1, 'John'), (2, 'Mike'), (3, 'Sarah') ``` 接下来，可以使用游标来遍历临时表。游标是一个类似指针的数据结构，可以逐行访问结果集。 ```sql DECLARE @ID INT DECLARE @Name VARCHAR(50) DECLARE TempCursor CURSOR FOR SELECT ID, Name FROM #TempTable OPEN TempCursor FETCH NEXT FROM TempCursor INTO @ID, @Name WHILE @@FETCH_STATUS = 0 BEGIN -- 在这里可以对每一行数据进行处理 PRINT 'ID: ' + CONVERT(VARCHAR(10), @ID) + ', Name: ' + @Name FETCH NEXT FROM TempCursor INTO @ID, @Name END CLOSE TempCursor DEALLOCATE TempCursor ``` 上述代码中，我们声明了两个变量`@ID`和`@Name`来存储临时表中的ID和Name列的值。然后，声明了一个游标`TempCursor`，并将临时表中的数据集合赋给游标。接着，我们打开游标，并使用`FETCH NEXT`语句将第一行数据读取到变量中。 在while循环中，我们可以对每一行数据进行处理。在这里，我们使用`PRINT`语句将ID和Name的值打印出来，你可以在这里进行其他的处理操作。 最后，我们关闭游标并释放它所占用的资源。 需要注意的是，在使用游标遍历临时表时，一定要记得关闭和释放游标，以避免资源泄漏和性能问题。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值