比较表变量和临时表（很不错）-白红宇

比较表变量和临时表（很不错）

阅读量：2286 次

发布时间：2019-05-09

本文共 6128 字，大约阅读时间需要 20 分钟。

来自：http://blog.csdn.net/roy_88/article/details/8461942

1、回滚事务对表变量无效，回滚事务对临时表有效

[sql]
 SET NOCOUNT ON;  
   
 DECLARE @TestTable TABLE (  
   RowID   INT IDENTITY PRIMARY KEY CLUSTERED,  
   Name     VARCHAR(9) NOT NULL UNIQUE,  
   Age     TINYINT NULL,  
   rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));  
   
 begin tran  
   
 INSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21);  
 rollback tran  
   
 SELECT 'Insert rows' as Action, * FROM @TestTable;  
 /*  
 Action  RowID   Name    Age rowguid  
 Insert rows 1   Roy 25  84C4F6CC-3AB3-4D7D-8779-0DCF3414AB61  
 Insert rows 2   中国风 21  1DB02A77-F20E-4326-A4E4-023CE9F5DEA7  
 */  
 go  
 if OBJECT_ID('Tempdb..#TestTable') is not null  
     drop table #TestTable  
 CREATE TABLE #TestTable (  
   RowID   INT IDENTITY PRIMARY KEY CLUSTERED,  
   Name     VARCHAR(9) NOT NULL UNIQUE,  
   Age     TINYINT NULL,  
   rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));  
    
  begin tran  
    
 INSERT INTO #TestTable (Name,Age) VALUES ('Roy', 25),('中国风', 21);  
 rollback tran  
 SELECT 'Insert rows' as Action,* FROM #TestTable  
 /*  
 无记录  
 */  

2、临时表支持用into生成临时表/TRUNCATE清空表/set identity_insert/drop table,表变量不支持

[sql]
 use tempdb  
 go  
 if OBJECT_ID('Tempdb..#TestTable') is not null  
     drop table #TestTable  
 --into  
 select 1 as ID into #TestTable  
   
 --TRUNCATE  
 TRUNCATE table #TestTable  
 go  
 if OBJECT_ID('Tempdb..#TestTable2') is not null  
     drop table #TestTable2  
 Create table #TestTable2(ID int identity,Num int)  
 go  
 set identity_insert #TestTable2 on;  
 insert into #TestTable2(ID,Num) values(1,20)  
 set identity_insert #TestTable2 off;  
 go  
 drop table #TestTable,#TestTable2  

3、表变量可以在创建表时定义主健PRIMARY KEY和唯一约束UNIQUE和检查check

[sql]
 use tempdb  
 go  
 SET NOCOUNT ON;  
 -- 记录当前tempdb里的表对象ID  
 if OBJECT_ID('Tempdb..#test') is not null  
     drop table #test  
 SELECT object_id   
   INTO #test   
   FROM tempdb.sys.tables;  
 GO  
   
 --创建表变量  
 DECLARE @temp TABLE (  
     RowID INT NOT NULL,  
     ColA INT NOT NULL check(ColA<10),  
     ColB char(1),  
     ColC char(1),  
     ColD char(1),  
     PRIMARY KEY NONCLUSTERED (RowID, ColA),  
     UNIQUE CLUSTERED (ColB, ColC),  
     UNIQUE NONCLUSTERED (ColC, ColD));  
   
 INSERT INTO @temp VALUES (1, 1, 'B', NULL, 'D');  
   
   
 --获取表变量对象的ID  
 DECLARE @object_id INT;  
 SELECT @object_id =   
   (SELECT object_id   
      FROM tempdb.sys.tables  
    EXCEPT  
    SELECT object_id   
      FROM #test);  
   
 -- 获取表变量的索引名  
   
 ;with IXC  
 as  
 (  
 SELECT  IXC.object_id,  
         IXC.index_id,  
         IXC.index_column_id,  
         IXC.is_descending_key,  
         IXC.is_included_column,  
         column_name = C.name  
   FROM tempdb.sys.index_columns IXC  
        JOIN tempdb.sys.columns C  
          ON IXC.object_id = C.object_id  
         AND IXC.column_id = C.column_id  
  WHERE IXC.object_id = @object_id  
 )  
 select   
         IX.object_id,  
         index_name = IX.name,  
         index_type_desc = IX.type_desc,  
         IX.is_unique,  
         IX.is_primary_key,  
         IX.is_unique_constraint,  
         IX.is_disabled,  
         index_columns = STUFF(IXC_COL.index_columns, 1, 2, N'')  
 from tempdb.sys.indexes as IX  
 CROSS APPLY(  
 SELECT index_columns =   
 (SELECT   
     N', ' + QUOTENAME(column_name)+ CASE is_descending_key WHEN 1 THEN N' DESC' ELSE N'' END  
 FROM IXC  
 WHERE object_id = IX.object_id AND index_id = IX.index_id AND is_included_column = 0 ORDER BY index_column_id FOR XML PATH(''), ROOT('r'), TYPE).value('/r[1]', 'nvarchar(max)')      
 )IXC_COL  
 where IX.object_id= @object_id  
 /*  
 object_id   index_name  index_type_desc is_unique   is_primary_key  is_unique_constraint    is_disabled index_columns  
 1572200651  UQ__#5DB5E0C__D87C70A35F9E293D  CLUSTERED   1   0   1   0   [ColB], [ColC]  
 1572200651  UQ__#5DB5E0C__287C70A3627A95E8  NONCLUSTERED    1   0   1   0   [ColC], [ColD]  
 1572200651  PK__#5DB5E0C__A5CBEAB465570293  NONCLUSTERED    1   1   0   0   [RowID], [ColA]  
 */  
 GO  
 drop table #test  

4、临时表名116个字符，表变量支持128个字符

[sql]
 CREATE TABLE #TestTable12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (  
 --                    ^         ^         ^         ^         ^         ^         ^         ^         ^         ^         ^         ^  
 --                   10         20        30        40        50        60        70        80        90       100       110       120  
   RowID INT IDENTITY PRIMARY KEY CLUSTERED,  
 );  
 GO  
 /*  
 消息 193，级别 15，状态 1，第 2 行  
 以 '#TestTable12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' 开头的对象名或列名太长。最大长度为 116 个字符。  
 */  
   
   
 declare      @TestTable1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 TABLE(  
 --                    ^         ^         ^         ^         ^         ^         ^         ^         ^         ^         ^         ^  
 --                   10         20        30        40        50        60        70        80        90       100       110       120  
   RowID INT IDENTITY PRIMARY KEY CLUSTERED);  

5、查看表变量的日志变化

CHECKPOINT将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。

[sql]
 USE tempdb   
 GO  
 ---- 截断日志，测试开始1  
 CHECKPOINT   
 -- 记录当前tempdb里的表对象ID    
 if OBJECT_ID('Tempdb..#test') is not null    
     drop table #test    
 SELECT object_id     
   INTO #test     
   FROM tempdb.sys.tables;  
 go  
 DECLARE @TransactionTest TABLE (  
 ID INT PRIMARY KEY,  
 SomeCol VARCHAR(20)  
 );  
 --获取表变量对象的ID  
 DECLARE @object_id INT;    
 SELECT @object_id =     
   (SELECT object_id     
      FROM tempdb.sys.tables    
    EXCEPT    
    SELECT object_id     
      FROM #test);   
        
 INSERT INTO @TransactionTest (ID, SomeCol) VALUES (0,'Row1');  
 INSERT INTO @TransactionTest (ID, SomeCol) VALUES (1,'Row2');  
   
 BEGIN TRANSACTION;  
 INSERT INTO @TransactionTest (ID, SomeCol) VALUES (2,'Row3');  
 ROLLBACK TRANSACTION;  
   
 SELECT * FROM @TransactionTest;  
   
 SELECT Operation, AllocUnitName, [Begin Time], [End Time]  
 FROM fn_dblog(NULL, NULL)  
 where AllocUnitName like '%'+object_Name(@object_id)+'%'  
 GO  

最后三条记录都是插入记录日志

[sql]
 USE tempdb   
 GO  
 -- 截断日志，测试开始2  
 CHECKPOINT   
 -- 记录当前tempdb里的表对象ID    
 if OBJECT_ID('Tempdb..#test') is not null    
     drop table #test    
 SELECT object_id     
   INTO #test     
   FROM tempdb.sys.tables;  
 go  
   
 DECLARE @TransactionTest TABLE (  
 ID INT PRIMARY KEY,  
 SomeCol VARCHAR(20)  
 );  
 --获取表变量对象的ID  
 DECLARE @object_id INT;    
 SELECT @object_id =     
   (SELECT object_id     
      FROM tempdb.sys.tables    
    EXCEPT    
    SELECT object_id     
      FROM #test);   
        
 INSERT INTO @TransactionTest (ID, SomeCol)  
 VALUES  
 (0,'Row1'),  
 (1,'Row2'),  
 (1,'Row3');--主健重复  
 SELECT * FROM @TransactionTest;  
 SELECT Operation, AllocUnitName, [Begin Time], [End Time]   
   FROM fn_dblog(NULL, NULL)  
   where AllocUnitName like '%'+object_Name(@object_id)+'%';  

插入第3条记录出错，有删除前两条的动作

6、临时表有两种类型：本地表和全局表。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

[sql]
 --本地临时表  
 if OBJECT_ID('Tempdb..#1') is not null  
     drop table #1  
 exec('select 1 as ID into #1')  
 select * from #1  
 /*  
 (1 行受影响)  
 消息 208，级别 16，状态 0，第 4 行  
 对象名 '#1' 无效。  
 */  
   
 --全局临时表  
 if OBJECT_ID('Tempdb..##1') is not null  
     drop table #1  
 exec('select 1 as ID into ##1')  
 select * from ##1  
 /*  
 ID  
 1  
 */  

你可能感兴趣的文章