比较表变量和临时表

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

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

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