比较表变量和临时表

1、1、回滚事务对表变量无效，回滚事务对临时表有效sql    SET NOCOUNT ON;    DECLARE TestTable TABLE (    RowID   INT IDENTITY PRIMARY KEY CLUSTERED,    Name     VARCHAR(9) NOT NULL UNIQUE,    Age     TINYINT NULL,    rowguid UNIQUEIDENTI

2、FIER 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   Nam

3、e    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

4、  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

5、    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,表变量不支持 

6、60;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')

7、 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,#TestT

8、able2  3、表变量可以在创建表时定义主健PRIMARY KEY和唯一约束UNIQUE和检查checksql use tempdb  go  SET NOCOUNT ON;  - 记录当前tempdb里的表对象ID  if OBJECT_ID('Tempdb.#test') is not null      drop table #test  SELECT object_id     INTO #test     F

9、ROM 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),  

10、60;   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

11、_id INT;  SELECT object_id =     (SELECT object_id        FROM tempdb.sys.tables     EXCEPT     SELECT object_id        FROM #test);    - 获取表变量的索引名    with IXC  as &

12、#160;(  SELECT  IXC.object_id,          IXC.index_id,          IXC.index_column_id,          IXC.is_descending_key,          IXC.is_included_column,          column_

13、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

14、  )  select           IX.object_id,          index_name = IX.name,          index_type_desc = IX.type_desc,          IX.is_unique,          IX.is_primary_key, &

15、#160;        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 =  

16、0;(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(''

17、;), ROOT('r'), TYPE).value('/r1','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

18、  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_A5CBEAB465570

19、293  NONCLUSTERED    1   1   0   0  RowID, ColA  */  GO  drop table #test   4、临时表名116个字符，表变量支持128个字符  sql  -                                

20、                                                                              -       

21、0;           10         20        30        40        50        60        70        80        90     

22、0; 100       110       120    RowID INT IDENTITY PRIMARY KEY CLUSTERED,  );  GO  /*  消息 193，级别 15，状态 1，第 2 行   */      declare       -               &

23、#160;                                                                                    

24、         -                   10         20        30        40        50        60        70    

25、60;  80        90       100       110       120    RowID INT IDENTITY PRIMARY KEY CLUSTERED);    5、查看表变量的日志变化 CHECKPOINT将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。 sql USE tempdb  &

26、#160;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; &

27、#160;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   &

28、#160;   EXCEPT       SELECT object_id          FROM #test);          INSERT INTO TransactionTest (ID, SomeCol) VALUES (0,'Row1');  INSERT INTO TransactionTest (ID, SomeCol) VALUES (1,'Row2'

29、);    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) 

30、0;where AllocUnitName like '%'+object_Name(object_id)+'%'  GO   最后三条记录都是插入记录日志 sql    USE tempdb   GO  - 截断日志，测试开始2  CHECKPOINT   - 记录当前tempdb里的表对象ID    if OBJECT_ID('Tempdb.#test') is not null    

31、;    drop table #test    SELECT object_id       INTO #test       FROM tempdb.sys.tables;  go    DECLARE TransactionTest TABLE (  ID INT PRIMARY KEY,  SomeCol VARCHAR(20)  );  -获取表变量对象的ID    

32、  DECLARE object_id INT;    SELECT object_id =       (SELECT object_id          FROM tempdb.sys.tables       EXCEPT       SELECT object_id          FROM #test);