SQL SERVER 表变量和临时表

一、表变量

1. 表变量定义

       表变量，是SQL Server 2000以来引入的概念，表变量本质是一个变量，它具有正式表对象的很多属性。如：它有表字段、字段数据类型、字段宽度、主键、唯一约束、NULL、NOT NULL约束、CHECK和DEFAULT约束。但是，表变量不支持约束命名，不支持索引，不支持外键，不支持表变量定义后的任何表变量结构的修改，仅可做数据的DML操作。

2. 表变量的使用场景

       需要在当前会话临时缓存少量的中间数据结果集，供当前会话多次使用这同一数据集或者同一数据结果集的一部分时，我们可以考虑使用表变量，表变量中的数据是缓存在内存中（大部分情况下如此，也会存储到物理磁盘中）。注意这里是少量数据集，不是大量结果集，建议是最好不要超过1000条数据记录。

3. 表变量使用范例

DECLARE @stuinfo TABLE
(id INT IDENTITY(1,1) PRIMARY KEY,sname NVARCHAR(20),gender NVARCHAR(1),sgroup INT,salary DECIMAL)
insert into @stuinfo
select  '张三','m',1,10000 union all
select  '李四','f',1,13000 union all
select  '王五','f',2,8500 union all
select  '赵六','m',3,12000 union all
select  '黄七','m',3,5000


SELECT  *  FROM  @stuinfo
SELECT id,sname,CASE gender WHEN 'm' THEN '男' WHEN 'f' THEN '女' END AS '性别' FROM @stuinfo
SELECT sname
,CASE 
    WHEN gender='m' AND sgroup = '1' THEN '第一组男生'
    WHEN gender='f' AND sgroup = '1' THEN '第一组女生'
    WHEN gender='m' AND sgroup = '2' THEN '第二组男生'
    WHEN gender='f' AND sgroup = '2' THEN '第二组女生'
    WHEN gender='m' AND sgroup = '3' THEN '第三组男生'
    WHEN gender='f' AND sgroup = '3' THEN '第三组女生'
END
 FROM @stuinfo

DECLARE @ordertype VARCHAR(1) = 'f'
SELECT * FROM @stuinfo
ORDER BY CASE WHEN @ordertype = 'm' THEN gender END DESC,CASE WHEN @ordertype = 'f' THEN gender END ASC; 

SELECT 
SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS '男生',
SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS '女生' 
FROM @stuinfo

UPDATE @stuinfo SET salary = CASE gender WHEN 'm' THEN salary*0.9 WHEN 'f' THEN salary*1.2 END
WHERE 1=1
SELECT * FROM @stuinfo

 

二、临时表

1. 临时表定义

       SQL Server的临时表是一种特殊的表，表名字是以#或者##打头。无论临时表在哪个数据库下创建，SQL Server均把临时表结构信息和数据存储在Tempdb数据库下。以#打头的临时表称为局部临时表，这种类型的临时表仅当前进程可见，其他进程不可访问，生命周期会随着当前连接进程的关闭而消亡。以##打头的临时表称为全局，此类型的临时表对所有进程可见，当前进程和其他进程均可访问，生命周期是所有使用到全局临时表的连接完全关闭后，临时表消亡。

2.临时表的作用

       临时表的作用和表变量类似，均是用于暂时缓存数据。临时表中的数据会被储存在Tempdb的物理文件磁盘上，当需要数据读取时，SQL Server会将临时表中数据从磁盘文件读入SQL Server Buffer Pool中，然后返回给客户端。因此，临时表对数据的存储和读取会有物理的IO Write和IO Read的。临时表相较于表变量可以存储稍微大量一些的数据，比如数据量超过10万条记录数，数据空间占用量超过100MB。

3.临时表的使用

IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL

DROP TABLE #tb_table

GO

CREATE TABLE #tb_table(

RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

,ProductName NVARCHAR(50) NOT NULL UNIQUE

,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)

,Windth DECIMAL(4,2) NOT NULL

,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)

,Dimension AS (Length * Windth * Height)

,Indate DATETIME NOT NULL CONSTRAINT DF_tbTable DEFAULT(GETDATE())

,CONSTRAINT CK_Windth CHECK(Windth>0.0)

);



CREATE INDEX IX_ProductName

ON #tb_table(ProductName);

GO

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('A', 0.1, 0.2, 0.3);

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('B', 0.4, 0.5, 0.6);

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('C', 0.7, 0.8, 0.9);

IF OBJECT_ID('tempdb..##tb_table','U') IS NOT NULL

DROP TABLE ##tb_table

GO

SELECT  *   INTO ##tb_table   FROM   #tb_table;

UPDATE  A   SET Length = 2.5  FROM  #tb_table AS A   WHERE RowID = 1;

DELETE TOP(1)  AF   ROM   #tb_table AS AWHERE RowID = 2;



SELECT * FROM #tb_table;

SELECT * FROM ##tb_table