1、回滚事务对表变量无效，回滚事务对临时表有效
[sql] www.2cto.com
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
www.2cto.com
--创建表变量
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