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



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
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭