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

原创 2014年01月23日 14:28:32


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

数据中使用表的,常会遇到两种使用表的方法,就是使用临时表及表量。在实际使用的候,我如何灵活的在存储过程中运用它然它们实现的功能基本上是一的,可如何在一个存储过程中有候去使用临时表而不使用表量,有候去使用表量而不使用临时表呢?

  临时

  临时表与永久表相似,只是它的建是在Tempdb中,它只有在一个数据库连束后或者由SQL命令DROP掉,才会消失,否就会一直存在。临时表在建的候都会SQLServer的系日志,Tempdb中体,是分配在内存中的,它也支持物理的磁,但用在指定的磁里看不到文件。

  临时表分本地和全局两种,本地临时表的名称都是以“#”,只有在本地当前的用户连接中才是可的,当用例断开除。全局临时表的名称都是以“##”建后任何用都是可的,当所有引用表的用断开除。

  下面我来看一个临时表的例子:  

CREATE TABLE dbo.#News
  (
  
News_id int NOT NULL,
  
NewsTitle varchar(100),
  
NewsContent varchar(2000),
  
NewsDateTime datetime
  )

  临时表可以建索引,也可以定义统计数据,所以可以用数据定义语(DDL)的声明来阻止临时表添加的限制,束,并参照完整性,如主和外键约束。比如来,我们现在来#News表字段NewsDateTime来添加一个默GetData()当前日期,并且News_id添加一个主,我就可以使用下面的句: 

ALTER TABLE dbo.#News
  ADD
  
CONSTRAINT [DF_NewsDateTime] DEFAULT (GETDATE()) FOR[NewsDateTime],
  
PRIMARY KEY CLUSTERED
  
(
  
[News_id]
  
) ON [PRIMARY]
  GO

IF  EXISTS (SELECT * FROMtempdb..sysobjects  WHERE id =object_id('tempdb..##wzg_test') and type='U')

   begin

                      DROP TABLE[addon_scm].[##wzg_test]

                      select 'droptable ##wzg_test'

   end

else

   begin

                      CREATE TABLE  [addon_scm].[##wzg_test]

                      (

                                  id      int ,

                                  name     varchar(100)  

                      )

                      select 'createtable ##wzg_test'

   end

IF  EXISTS (SELECT * FROMtempdb..sysobjects  WHERE id =object_id('tempdb..##wzg_test') and type='U')

   Begin

  insert into [addon_scm].[##wzg_test] values(1,'wzh')

              select * from [addon_scm].[##wzg_test]

   end  

临时表在建之后可以修改多已定选项,包括:

  1)添加、修改、除列。例如,列的名称、度、数据型、精度、小数位数以及空性均可行修改,只是有一些限制而已。

  2)可添加或除主和外键约束。

  3)可添加或 UNIQUE CHECK 束及DEFAULT ()

  4)可使用IDENTITY ROWGUIDCOL 属性添加或标识符列。ROWGUIDCOL 属性也可添加至有列或从有列除,但是任何候在表中只能有一列可具有属性。

  5)表及表中所定的列已注册全文索引。

  

  表建的似于临时表,区就在于建的候,必之命名。表量是量的一种,表量也分本地及全局的两种,本地表量的名称都是以“@”,只有在本地当前的用户连接中才可以访问。全局的表量的名称都是以“@@”,一般都是系的全局量,像我常用到的,如@@Error代表错误的号,@@RowCount代表影响的行数。

  如我看看建表量的句:

 DECLARE @News Table
  (
  
News_id int NOT NULL,
  
NewsTitle varchar(100),
  
NewsContent varchar(2000),
  
NewsDateTime datetime
  )

  较临时表及表量都可以通SQL选择、插入、更新及句,它的的不同主要体在以下些:

  1)量是存在内存中的,当用访问量的候,SQLServer是不生日志的,而在临时表中是生日志的;

  2)在表量中,是不允有非聚集索引的;

  3)量是不允DEFAULT认值,也不允;

  4)临时表上的统计信息是健全而可靠的,但是表量上的统计信息是不可靠的;

  5)临时表中是有的机制,而表量中就没有的机制。

  我们现在来看一个完整的例子,来看它的用法的异同:

  利用临时  

CREATETABLE dbo.#News
  
(
  
News_id int NOT NULL,
  
NewsTitle varchar(100),
  
NewsContent varchar(2000),
  
NewsDateTime datetime
  
)
  
INSERT INTO dbo.#News (News_id, NewsTitle,NewsContent, NewsDateTime)
  
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
  
SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM dbo.#News
  DROP TABLE dbo.[#News]

  利用表 

DECLARE@News table
  
(
  
News_id int NOT NULL,
  
NewsTitle varchar(100),
  
NewsContent varchar(2000),
  
NewsDateTime datetime
  
)
  
INSERT INTO @News (News_id, NewsTitle, NewsContent,NewsDateTime)
  
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
  SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM @News

  我可以看到上面两种情况实现的是一的效果,第一种利用临时表的候,临时表一般被建后,如果在行的候,没有通DROPTable的操作,第二次就不能再被建,而定量也不需要DROPTable的操作,一次行完成后就会消失。

  其选择临时是表量的候,我大多数情况下在使用的候都是可以的,但一般我需要遵循下面个情况,选择对应的方式:

  1)使用表量主要需要考的就是用程序内存的力,如果代的运行例很多,就要特注意内存内存的消耗。我们对小的数据或者是通过计算出来的推荐使用表量。如果数据的果比大,在代中用于临时计算,在取的候没有什么分的聚合,就可以考使用表量。

  2)一般于大的数据果,或者因为统计出来的数据了便于更好的化,我就推荐使用临时表,同时还可以建索引,由于临时表是存放在Tempdb中,一般默分配的空很少,需要tempdb调优,增大其存的空

 

SQL Server 表变量和临时表系列之概念篇

问题引入 “菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了...
  • lishimin1012
  • lishimin1012
  • 2017年01月06日 14:37
  • 547

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server ...
  • lovehongyun
  • lovehongyun
  • 2008年01月09日 09:27
  • 10996

SQL SERVER临时表的使用&表变量的使用

临时表: 创建临时表可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个...
  • S630730701
  • S630730701
  • 2016年08月03日 16:57
  • 2455

临时表vs.表变量以及它们对SQLServer性能的影响

 问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server...
  • lainY7mail
  • lainY7mail
  • 2008年04月17日 12:30
  • 1908

SQL Server 变量表 临时表 分析

最近,我有一朋友,对我说他的数据库中的很多存储过程,执行都是超时.让我替他看看是什么原因.我一看,原来他的存储过程中用了很多的临时表与变量表.于是我跟他说过犹不及. 在存储过程中使用临时表或变量表,...
  • avon520
  • avon520
  • 2012年04月06日 14:05
  • 2919

Sql表变量和临时表

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

sqlserver 中临时表、临时变量和with as关键词创建“临时表”的区别

SQL数据库中数据处理时,有时候需要建立临时表,将查询后的结果集放到临时表中,然后在针对这个数据进行操作。   创建“临时表”(逻辑上的临时表,可能不一定是数据库的)的方法有一下几种:  ...
  • miqi770
  • miqi770
  • 2016年05月26日 15:39
  • 3968

创建临时表,大数据的临时表与小数据量的表变量

1.  将数据倒入表中的方法: 已经存在的表:select * into #tempMail from MailLog where ActionFlag = N   不存在的表:create tabl...
  • lishuaide0517
  • lishuaide0517
  • 2010年04月07日 10:32
  • 486

一些不常用的T-sql(临时表,用变量表示表名,判断表,字段是否存在,sql启动job

--创建本地临时表#tb --当#tb存在就删除#tb,不存在就新建 if object_id('tempdb..#tb') is not null drop table #tb else ...
  • xxhysj
  • xxhysj
  • 2012年11月24日 09:40
  • 2230

sqlserver临时表或表变量代替游标

为什么要用游标呢?数据量超过1万,游标就很慢了。 在很多场合,用临时表或表变量也可以替代游标 临时表用在表没有标识列(int)的情况下. 在表有标识列(int)的情况下可以用表变量,当然也可...
  • liangweiwei130
  • liangweiwei130
  • 2011年08月18日 14:14
  • 5088
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server中临时表与表变量的区别
举报原因:
原因补充:

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