比较表变量及临时表.docVIP

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(ColA10), ? ? ? ColB char(1), ? ? ? ColC char(1), ? ? ? ColD c