【个人学习笔记12之--临时表和表变量】

今天来说说 临时表和表变量
当我们需要把数据临时具体化的时候,你会想到临时表,表变量或者表表达式
处理成这样的好处有几个：
1.处理临时出来的表 比基本数据肯定要简洁。特别是你多次利用它的时候,你可以想象经常从一张大表里找东西有多浩大的工程.
2.处理成临时数据集 可以让你的代码,思路清晰无比. 有些代码比较长,如果你分模块逐个处理会很清楚.这里临时数据集起到很重要的作用.

 

A.临时表

1. 你建的临时表都是放在TEMPDB中。所以当我们用临时数据集的时候是可以减轻你那个用户数据库的压力的。

2. 你的临时表在判断是否存在时,要用IF OBJECT_ID('TEMPDB..#t') is not null -->>注意这里一定要用TEMPDB..临时表名.

3. 你在一个会话建立的临时表只在该会话有效.意思就是你可以在另外一个会话建立同名的一个临时表.你也许会奇怪,sql 内部
   又是怎么知道哪个是哪个临时表的呢？其实你每次建立一个临时表,它就会在tempdb中为表名加上下划线和数字后缀.如：
   我分别在MASTER和PUBS库中分别建立了1个临时表#p
use tempdb go select name from sys.objects where name like '#p%' /* name --------------------- #p_____________________________________00000000000C #p_______________________________________000000000011 */

4.临时表只有在创建他的那个级别和内部级别可见.
 什么意思呢？就是说如果你在一个存储过程内部建立的临时表在存储过程外面是看不到的。当你存储过程执行完了,这个临时表就玩完了.
 而你在会话的最外层建立的临时表 那再找个会话中的任何地方都是可以用它的.只有当会话关掉的时候,它才结束.--| 当然你可以手动删除它.
 
 这里就可以引出一个有意思的问题:既然临时表可见度不一致,那么我们就可以在一个会话里建立同名的临时表了,只要创建级别不一样.
 但是还是会那么点问题.首先举个例子来说明这个问题:(引用书上例子,比较简洁)
 SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.proc1') IS NOT NULL DROP PROC dbo.proc1; GO IF OBJECT_ID('dbo.proc2') IS NOT NULL DROP PROC dbo.proc2; GO CREATE PROC dbo.proc1 AS CREATE TABLE #T1(col1 INT NOT NULL); INSERT INTO #T1 VALUES(1); SELECT * FROM #T1; EXEC dbo.proc2;--注意这里我调用了RPOC2 GO CREATE PROC dbo.proc2 AS CREATE TABLE #T1(col1 INT NULL);--这里再次创建了#T1 INSERT INTO #T1 VALUES(2); SELECT * FROM #T1; GO -- 调用存储过程1 EXEC dbo.proc1; GO /* col1 ----------- 1 col1 ----------- 2 */  
 ---这里似乎没出问题.这里他们的表结构式一样的,注意.
 --现在情况变了,我去修改下第二个存储过程中#T1的表结构,看看情况还是否一样.
 ALTER PROC dbo.proc2 AS CREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL); INSERT INTO #T1 VALUES(2, 2); SELECT * FROM #T1; GO -- 运行,出现错误. EXEC dbo.proc1; GO /* col1 ----------- 1 消息 213，级别 16，状态 1，过程 proc2，第 5 行 列名或所提供值的数目与表定义不匹配。 */   

 

--这里的错误明显发生在PROC1调用PROC2的时候.这个错误情况看来,貌似我们的#t1表的列数还是1列 而不是2列.
 解释：知识前提：当你的批处理被解析时候,你创建的临时表是不可以用的,它要等到运行时候才被解析.如果你引用的临时表已经存在了,它
 不会智能地去帮你判断下你本身是否有这么个临时表,而是直接去解析那个已经存在的临时表.然后就悲剧般的使用已经存在的表.
 我们回到我们这个题目,当PROC2被PROC1调用的时候,因为#t1表已经在PROC1中存在了,那么它在解析你的PROC2中的INSERT INTO #T1 VALUES(2, 2)
 的时候去插入的是PROC1中的#T1。这个时候你就报错了.

 ----我们的解决方法就是：先去运行下PROC2 然后在执行  EXEC dbo.proc1; 就是你要的了.这是因为先执行PROC2，他的执行计划就存在缓存里了.
 这个问题就告诉我们在不同级别里取相同名字的临时表也是不明智的.

5.临时表是属于事务的组成部分,因此是否把它当成外部事务的一部分操作,时你考虑是否选择临时表的一个重要因素.

6.临时表的分部统计信息是被优化器维护的,它可以生成优化的计划,这也是区别于表变量的一个特性之一.
   这里要注意点：就像存储过程一样,临时表到了一定程度的改变后,它需要重新编译.这里简单介绍下达到重新编译的标准.
   重新编译阀值(RT)是根据表的类型和行数所确定的.
   a-->持久表:n<=500行的时候,RT=500; n>500,RT=500+0.2*n; (n是表的基数)
   b-->临时表:n<=6的时候,RT=6 ; 6<n<=500,RT=500；n>500,RT=500+0.2*n
  比如你在临时表了加载了6行,当增加第7行的时候就会导致重新编译~  

7.动态中更改临时表的架构(表结构)
DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000); SET @schema = 'col1 INT, col2 DECIMAL(10, 2)'; SET @insert = 'INSERT INTO #T42 VALUES(10, 20.30)'; CREATE TABLE #T42(dummycol INT); -- 会话外层创建临时表 -- Alter #T 增加你需要的列 -- Alter #T 删除不需要的列 -- 插入数据 EXEC(' ALTER TABLE #T42 ADD ' + @schema + '; ALTER TABLE #T42 DROP COLUMN dummycol; ' + @insert ); GO SELECT * FROM #T42; 
 
 ---这个时候会出现--列名或所提供值的数目与表定义不匹配--的错误.说明不能使用刚才修改过的表结构.这个原因我在上面说过了,因为外层还有一个#t 它在
 ---解析这个动态的INSERT时候，会找到这个会话已经拥有的#T,而新的表结构是没用到的.
 --解决方法--
 --你可以人为制造一个更加低级的环境,我的意思就是在这个动态内部加个动态,这样他要去解析的时候再里面找不到就跑到最接近他的那层.这样他就可以解析到新的那个表结构
 DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000); SET @schema = 'col1 INT, col2 DECIMAL(10, 2)'; SET @insert = 'INSERT INTO #T42 VALUES(10, 20.30)'; CREATE TABLE #T42(dummycol INT); EXEC(' ALTER TABLE #T42 ADD ' + @schema + '; ALTER TABLE #T42 DROP COLUMN dummycol; EXEC(''' + @insert + ''')'); GO SELECT * FROM #T42; ---- /* col1 col2 ----------- ----- 10 20.30 */

 

8.全局临时表:它用在会话之间共享数据又没安全担心的情况下。它跟局部临时表不同之处在于：
 a.因为多个会话都可见,所以不可以创建多个同名的全局临时表;
 b.全局临时表是在会话结束时候才会消亡.比如说你在一个存储过程里创建一个全局的临时表,存储过程结束了,但是它不会像局部临时表一样马上消亡.
 c.如果你想让你每次用SQL的时候总有这么一个的全局临时表一直存在,在里面放些重要的东西.这个时候你就要做到2点:它不属于任何一个会话;它在启动时候自动存在.

  USE master; GO IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL DROP PROC dbo.sp_Globals GO CREATE PROC dbo.sp_Globals AS CREATE TABLE ##Globals ( varname sysname NOT NULL PRIMARY KEY, val SQL_VARIANT NULL ); GO  

 --在执行时候用startup标记该存储过程,SQL每次启动都会调用这个存储过程了就会
 EXEC dbo.sp_procoption 'sp_Globals', 'startup', 'true';
 GO
 -- 然后你重启下 SQL Server
 -- 添加数据
SET NOCOUNT ON; INSERT INTO ##Globals VALUES('var1', CAST('abc' AS VARCHAR(10))); SELECT * FROM ##Globals;  

 --关掉SQL 再开
INSERT INTO ##Globals VALUES('var2', CAST('abc' AS VARCHAR(10))); SELECT * FROM ##Globals; 
 -----我们可以把这个全局临时表当做一个能够存储全局变量的表.

 

 

B.表变量

 1.表变量上是不能创建显示的索引的,只能建PRIMARY KEY和UNIQUE约束;

 2.声明了表变量后 你无法在修改它的架构.
  declare @s table(a int)  alter table @s add k int 这种做法是不可取的.所以每次创建都要考虑全面,一击必中.

 3.大家也许也碰到过表变量是不能做列的限定名称的.select @s.a from @s 错误 可以给@s 取个别名 然后限定.

 4.表变量在TEMPDB中是确实存在物理的表现形式.
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%#%'; /* TABLE_NAME ----------------- ##Globals */ GO DECLARE @T TABLE(col1 INT); INSERT INTO @T VALUES(1); SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%#%'; GO /* TABLE_NAME ---------------- ##Globals #0CBAE877 */ 
  ---通过上面的代码 你会发现 第一次你在TEMPDB的架构信息表里只能找到上面我们创建的全局临时表 第二次我们建立了个表变量,
  ---然后在查询,发现他确实存在了.而不是存在在内存中;而且它的名字形式居然和临时表一样.神奇了.

 5.它只能在创建级别中可见,这个跟一般的变量时一样的.它不同于临时表,在内部级别可见的性质.

 6.表变量不同于临时表的还有一点就是它不属于事务的一部分,就是说对表变量的操作,语句又是外部事务的一部分,那么更改不会回滚了,永久生效.

 7.使用表变量场合：
 a.优化器不需要一些统计信息就可以生成最佳计划的时候--返回表里所有行；唯一索引的列上执行查询;聚集索引的范围查询;这个时候只会产生重新编译的开销.
 b.表非常非常小的时候;
 
C.关于tempdb数据库

 a.SQL2005中这个数据库非常重要的.存储了很多东西.--书上几点
  1.临时表和表变量被物理地存在里面;
  2.作为查询计划一部分的脱机数据;
  3.维护行版本.

 b.注意最好把tempdb数据库放在单独的磁盘里，特别是在使用TEMPDB频繁的地方.

 c.注意随时修改tempbd数据库的大小,根据实际大小。

 d.由于tempdb数据库在SQL重启的时候会被清理,所以注意你在里面创建的想保存的一些表和函数 存储过程.你可以再model中创建他们,这样在每次启动的时候，
  你的tempdb数据库中就存在他们了,不过这样会影响其他库的使用;还有一个方法我上面说过了,启动的时候调用它们.

 

 

D.表表达式.
  因为我上面也提到了临时存数据的时候可以用表表达式.它比较合适2个地方:
  1.为了简化操作,使得代码看起来简洁 清楚.
  2.当你很少需要访问这个临时数据集的时候.
  还有一点：它不会在tempdb中物理化表示.