表变量与临时表的优缺点

转载 2012年03月29日 16:24:36
表变量:   

  DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))   
  INSERT @tb   

  SELECT id, name 
  FROM mytable  

  WHERE name like ‘zhang%’  

 

临时表:  

  SELECT name, address
  INTO #ta   FROM mytable  
  WHERE name like ‘zhang%’

表变量和临时表的比较:

  • 临时表是利用了硬盘(tempdb数据库) ,表名变量是占用内存,因此小数据量当然是内存中的表变量更快。当大数据量时,就不能用表变量了,太耗内存了。大数据量时适合用临时表。
  • 表变量缺省放在内存,速度快,所以在触发器,存储过程里如果数据量不大,应该用表变量。
  • 临时表缺省使用硬盘,一般来说速度比较慢,那是不是就不用临时表呢?也不是,在数据量比较大的时候,如果使用表变量,会把内存耗尽,然后使用 TEMPDB的空间,这样主要还是使用硬盘空间,但同时把内存基本耗尽,增加了内存调入调出的机会,反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间,然后使用临时表。
  • 临时表相对而言表变量主要是多了I/O时间,但少了对内存资源的占用。数据量较大的时候,由于对内存资源的消耗较少,使用临时表比表变量有更好的性能。
  • 建议:触发器、自定义函数用表变量;存储过程看情况,大部分用表变量;特殊的应用,大数据量的场合用临时表。
  • 表变量有明确的作用域,在定义表变量的函数、存储过程或批处理结束时,会自动清除表变量。
  • 在存储过程中使用表变量与使用临时表相比,减少了存储过程的重新编译量。
  • 涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
  • 表变量需要事先知道表结构,普通临时表,只在当前会话中可用与表变量相同into一下就可以了,方便;全局临时表:可在多个会话中使用存在于temp中需显示的drop。(不知道表结构情况下临时表方便一些)
  • 全局临时表的功能是表变量没法达到的。
  • 表变量不必删除,也就不会有命名冲突,临时表特别是全局临时表用的时候必须解决命名冲突。
  • 应避免频繁创建和删除临时表,减少系统表资源的消耗。
  • 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。
  • 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。
  • 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。
  • 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。

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

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

比较表变量和临时表

1、回滚事务对表变量无效,回滚事务对临时表有效SET NOCOUNT ON; DECLARE @TestTable TABLE ( RowID INT IDENTITY PRIMARY KE...
  • roy_88
  • roy_88
  • 2013年01月03日 19:13
  • 3995

SQL Server 表变量与临时表区别

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

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

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

表变量与临时表的优缺点?

什么情况下使用表变量?什么情况下使用临时表? 表变量:     DECLARE @tb  table(id   int   identity(1,1), name   varchar(100)...
  • a369414641
  • a369414641
  • 2014年12月05日 11:00
  • 279

表变量与临时表的优缺点

  • lee576
  • lee576
  • 2006年10月14日 21:50
  • 925

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

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

Sql表变量和临时表

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

MySQL中的临时表使用方法

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后多这些表运行查询。...
  • JimyJimang
  • JimyJimang
  • 2012年02月21日 18:06
  • 1060

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

1.  将数据倒入表中的方法: 已经存在的表:select * into #tempMail from MailLog where ActionFlag = N   不存在的表:create tabl...
  • lishuaide0517
  • lishuaide0517
  • 2010年04月07日 10:32
  • 484
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:表变量与临时表的优缺点
举报原因:
原因补充:

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