SqlServer 存储临时数据

JavaDaBaiCai

已于 2025-01-07 13:06:19 修改

阅读量855

点赞数 5

文章标签： sqlserver

于 2024-11-14 16:38:13 首次发布

本文链接：https://blog.youkuaiyun.com/JavaDaBaiCai/article/details/143774906

版权

WITH 子句中的公用表表达式（CTE）、临时表（Temporary Table）和表变量（Table Variable）都可以存储临时数据，但它们的使用场景、生命周期、作用范围和性能特性有所不同。下面是这三者之间的主要区别：

公用表表达式（CTE）
定义：
CTE 是一个临时的查询结果集，仅在当前的 SQL 查询或语句的执行周期内存在。
CTE 通常用于提高查询的可读性、简化复杂查询结构，或者避免重复的子查询。
特点：
作用范围：仅在定义 CTE 的查询语句内有效，查询结束后 CTE 就不再存在。
生命周期：Cte 只在当前查询中生效，不能跨多个查询或会话使用。
性能：CTE 并不真正创建物理结构，它只是一个临时的命名查询。每次执行时，它会重新计算，因此对于大数据量查询时，可能导致性能问题。
递归查询支持：CTE 支持递归查询，可以处理层级数据结构。
示例：
sql

WITH CTE AS (
    SELECT id, name FROM employees WHERE department = 'HR'
)
SELECT * FROM CTE;

临时表（Temporary Table）
定义：
临时表是数据库中一个实际的表结构，存在于会话中，可以用于存储中间结果。
临时表可以在一个会话中多次使用，并且可以在不同的查询之间共享。
特点：
作用范围：临时表的作用范围是当前数据库会话。会话结束时，临时表会自动删除。
生命周期：在数据库会话中创建，直到会话结束或明确删除临时表。
性能：临时表的创建涉及磁盘操作，虽然 SQL Server 使用内存存储临时表，但对于大数据量操作，临时表通常比 CTE 更高效，尤其是多次引用时。
可持久性：可以在会话内的多个查询中使用，但会话结束后临时表会被自动删除。
示例：
sql

CREATE TABLE #TempEmployees (id INT, name VARCHAR(100));

INSERT INTO #TempEmployees (id, name)
SELECT id, name FROM employees WHERE department = 'HR';

SELECT * FROM #TempEmployees;

DROP TABLE #TempEmployees;

表变量（Table Variable）
定义：
表变量是一种类似于临时表的数据结构，但它的生命周期仅限于当前的存储过程、批处理或函数中。
表变量通常用于存储较小的数据集，且在存储过程或查询中使用。
特点：
作用范围：表变量的作用范围通常是当前的存储过程、函数或批处理块。它在这些块内有效，结束后会自动销毁。
生命周期：表变量的生命周期非常短，它仅在声明的存储过程或批处理执行时存在。
性能：表变量通常比临时表更轻量级，因为它们直接存储在内存中，减少了磁盘I/O。对于小数据集来说，表变量的性能更好，但对于大数据集，表变量可能导致性能问题，因为它不支持索引优化。
限制：表变量不能使用 ALTER 语句修改，也不支持某些特性（例如，TRIGGERS 和 FOREIGN KEY）。
可持久性：表变量的作用范围在当前查询、存储过程或批处理中，结束后会自动销毁。
示例：
sql

DECLARE @TempEmployees TABLE (id INT, name VARCHAR(100));

INSERT INTO @TempEmployees (id, name)
SELECT id, name FROM employees WHERE department = 'HR';

SELECT * FROM @TempEmployees;

在这里插入图片描述

选择哪个？
使用 CTE：当需要简化查询结构或者进行递归查询时，使用 CTE 是最佳选择。它的可读性高，适用于单次查询。
使用临时表：当需要存储大量数据并跨多个查询或操作引用时，使用临时表更合适。临时表支持索引，能够处理更复杂的操作。
使用表变量：当数据量较小且需要轻量级临时存储时，表变量是一个不错的选择。它的性能较好，但不支持索引。

临时表补充

私有临时表（#）
定义：
私有临时表是以单个 # 开头的表名，例如：#TempTable。
这种类型的表只对创建它的会话（session）可见。换句话说，只有当前的数据库连接可以访问该表。
私有临时表在会话（或连接）结束时自动被删除。
特点：
作用域：只在创建它的会话内可见。
生命周期：在会话结束时自动删除。
共享性：不能被其他会话或连接访问。
示例：

sql
-- 创建私有临时表
CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);

-- 插入数据
INSERT INTO #TempTable (ID, Name)
VALUES (1, 'Alice'), (2, 'Bob');

-- 查询数据
SELECT * FROM #TempTable;

-- 会话结束时，#TempTable 会被自动删除。

全局临时表（##）
定义：
全局临时表是以双 ## 开头的表名，例如：##GlobalTempTable。
这种类型的表对所有会话都可见，任何连接到 SQL Server 实例的用户都可以访问它。
全局临时表在最后一个引用它的会话结束时才会被删除。
特点：
作用域：对所有会话可见。
生命周期：只有在最后一个会话关闭后才会被删除。
共享性：所有会话都可以访问和操作它。
示例：

sql
-- 创建全局临时表
CREATE TABLE ##GlobalTempTable (
    ID INT,
    Name NVARCHAR(50)
);

-- 插入数据
INSERT INTO ##GlobalTempTable (ID, Name)
VALUES (1, 'Alice'), (2, 'Bob');

-- 任何其他会话都可以访问这个全局临时表
SELECT * FROM ##GlobalTempTable;

– 只有最后一个引用该表的会话结束后，表才会被自动删除。
比较：私有临时表（#）与全局临时表（##）
在这里插入图片描述

注意事项：
私有临时表：适用于临时存储和处理数据，不需要共享给其他会话的场景。比如在存储过程或脚本中使用。
全局临时表：适用于多个会话需要共享数据的情况。例如，当一个会话生成了一些临时数据，其他会话也需要使用这些数据时，可以使用全局临时表。
清理：
私有临时表会在会话结束时自动删除。
全局临时表只有在所有会话都不再引用它时才会删除，因此可能需要手动清理，或者等待 SQL Server 自动删除它。

IF OBJECT_ID(N'TEMPDB..##TBL') IS NOT NULL
	DROP TABLE ##TBL

实际应用：
使用私有临时表（#）是最常见的做法，适用于不会跨会话或连接共享数据的临时操作。
使用全局临时表（##）时，要小心避免数据泄漏和竞争条件，特别是在多个用户或会话需要同时访问时。