Temporary Table在SQLServer中的应用-猿码集

1. 什么是Temporary Table？

Temporary Table（临时表）在SQLServer中是一种特殊的表，其生命周期仅限于当前数据库会话。它通常是在内存中创建、操作和删除的，因此不会像普通的表那样持久存在于数据库中。在SQLServer中，可以创建本地临时表和全局临时表，前者是特定会话所使用的，后者是所有会话都可以使用的。临时表不仅可以用于查询操作，还可以在存储过程、触发器和用户定义函数中使用。

2. 如何创建Temporary Table？

2.1 创建本地临时表

创建本地临时表的语法格式如下：


CREATE TABLE #tempTableName
(
    column1 datatype1,
    column2 datatype2,
    column3 datatype3,
   ......
)

其中#tempTableName是表名，以#号开头，表示这是一个本地临时表，其作用范围限制在创建它的会话中。column1、column2等是表的列名，datatype1、datatype2等是列的数据类型。对于本地临时表，只有拥有这个表的会话可以访问它。

例如，我们可以创建一个名为#temp的本地临时表：


CREATE TABLE #temp 
(
    id int,
    name varchar(50),
    age int
)

2.2 创建全局临时表

创建全局临时表的语法格式如下：


CREATE TABLE ##tempTableName
(
    column1 datatype1,
    column2 datatype2,
    column3 datatype3,
   ......
)

其中##tempTableName是表名，以##号开头，表示这是一个全局临时表，其作用范围是所有会话。column1、column2等是表的列名，datatype1、datatype2等是列的数据类型。对于全局临时表，所有会话都可以访问它。

例如，我们可以创建一个名为##temp的全局临时表：


CREATE TABLE ##temp 
(
    id int,
    name varchar(50),
    age int
)

3. 如何使用Temporary Table？

临时表的使用方式和普通表是一样的，可以进行查询、插入、更新、删除等操作。下面分别介绍如何进行这些操作。

3.1 查询Temporary Table

查询临时表的语法和普通表的查询语法是相同的，例如：


SELECT * FROM #temp
SELECT * FROM ##temp

可以按照普通表的方式进行筛选、聚合等操作。

3.2 插入数据到Temporary Table

向临时表中插入数据的语法和普通表是相同的，例如：


INSERT INTO #temp (id,name,age) VALUES (1,'Tom',20)
INSERT INTO #temp (id,name,age) VALUES (2,'Jerry',22)

可以一次性插入多行数据：


INSERT INTO #temp (id,name,age) VALUES 
(1,'Tom',20),
(2,'Jerry',22)

3.3 更新Temporary Table

更新临时表的语法和普通表是相同的，例如：


UPDATE #temp SET age = 18 WHERE name = 'Tom'

3.4 删除Temporary Table

删除临时表的语法和普通表是相同的，例如：

DROP TABLE #temp DROP TABLE ##temp

4. 临时表的应用场景

临时表的应用场景很多，下面介绍一些常见的应用场景。

4.1 存储过程中使用临时表

存储过程中经常需要先将数据存放到临时表中，再进行一些处理和计算，最后再将结果返回。例如：


CREATE PROCEDURE sp_get_avg_salary
AS
BEGIN
    CREATE TABLE #temp (salary INT)
    
    INSERT INTO #temp (salary)
    SELECT salary FROM employee
    
    SELECT AVG(salary) as avg_salary FROM #temp
    
    DROP TABLE #temp
END

这里我们创建了一个存储过程sp_get_avg_salary，先创建了一个名为#temp的临时表，将employee表中的salary插入到临时表中，最后计算#temp表中salary的平均值，最后删除临时表。

4.2 级联更改多个表时使用临时表

有时候在进行多个表的级联更改时，我们需要先将一些中间结果存放到临时表中，再进行下一步操作。例如：


BEGIN TRANSACTION
CREATE TABLE #temp (name varchar(50), new_salary INT)
INSERT INTO #temp (name,new_salary)
SELECT e.name, e.salary * 1.1 as new_salary
FROM employee e
UPDATE employee
SET salary = t.new_salary
FROM employee e 
    JOIN #temp t ON e.name = t.name
DROP TABLE #temp
COMMIT TRANSACTION

这里我们在对employee表进行加薪操作时，先将中间结果存放到一个名为#temp的临时表中，然后再利用这个临时表进行更新操作。这样做是为了避免直接在employee表上进行更新操作，导致更新出现问题时无法恢复。

5. 总结

临时表在SQLServer中具有很多应用场景，可以作为中间结果存储或者临时存储数据。使用临时表可以简化查询操作，提高查询性能，同时保证数据的安全性和可恢复性。

Temporary Table在SQLServer中的应用