SQLServer表变量与临时表

表变量

变量都以@或@@为前缀，表变量是变量的一种，另外一种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者日期型DateTime)。以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，通常都是系统变量，比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界，超出了这个边界，表变量也就消亡了。

表变量存放在内存中，正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。

DECLARE @TestTable TABLE (
  RowID   INT IDENTITY PRIMARY KEY CLUSTERED,
  Name     VARCHAR(9) NOT NULL UNIQUE,
  Age     TINYINT NULL,
  rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));

临时表

临时对象都以#或##为前缀，临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在系统数据库的tempdb中。以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。

我们前面说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。

Create table #TestTable2(ID int identity,Num int)

回滚事务对表变量无效，回滚事务对临时表有效
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
/*
无记录
*/


--临时表也支持创建主键 索引 约束
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');
INSERT INTO @temp VALUES (1, 2, 'B', NULL, 'D');
INSERT INTO @temp VALUES (11, 12, 'B', 'C', 'D');

当数据记录行数过大时建议使用临时表的方式处理，避免过度使用内存。