SqlServer 存储临时数据：WITH 子句（CTE）、临时表（Temporary Table）、 表变量（Table Variable）

SqlServer 存储临时数据：WITH 子句   （CTE）、临时表（Temporary Table） 和 表变量（Table Variable）

区别：

 

	WITH 子句（公用表表达式，CTE）	临时表（Temporary Table）	表变量（Table Variable）
	定义： 公用表表达式（Common Table Expressions, CTE）是一种临时的结果集，它在查询执行期间存在。CTE 可以用作临时结果集来简化复杂的查询。   作用范围：当前查询 不支持索引 支持递归查询 不持久 简化复杂查询（避免重复的子查询）	定义： 临时表是在 SQL Server 中创建的特殊表，用于存储临时数据  类型：局部临时表（以单个井号 # 开头）和全局临时表（以两个井号 ## 开头）。局部临时表在创建它们的会话结束时自动删除，而全局临时表在 SQL Server 实例关闭时删除。   作用范围：（局部临时表）当前会话 支持索引 支持修改数据结构 不支持 持久 简化大量中间数据 跨查询使用	定义： 表变量是存储在内存中的临时结构，用于存储数据。每个表变量都有自己的作用域，通常限于其被声明的批处理或存储过程中。   作用范围： 声明所在的批处理或存储过程中 不支持索引 不支持 不持久 简化少量中间数据 轻量级数据
	优点： 可读性高：CTE 可以使复杂的查询更易于理解和维护。 性能：在某些情况下，CTE 可以提高查询性能，尤其是在多次引用同一数据集时。 递归查询：非常适合于编写递归查询。 缺点： 作用域限制：CTE 的作用域限于其被定义的查询块内。 并发限制：在多用户环境中，如果多个用户同时使用相同的 CTE，可能会导致性能问题或资源争用。	优点： 灵活性：可以像普通表一样对临时表进行索引、约束等操作。 持久性：可以跨多个查询使用，特别是在需要多次引用同一数据集的情况下。 并发性：不受单个查询块的作用域限制，可以在多个查询中使用。 缺点： 开销：创建和管理临时表的开销比 CTE 大。 资源占用：如果忘记删除，可能会占用不必要的资源。 性能影响：在高并发环境中，过多的临时表可能会影响性能。	优点： 内存操作：数据存储在内存中，访问速度快。 作用域限制：作用域清晰，只在声明它的批处理或存储过程中有效。 简单性：声明和使用都很简单。 缺点： 内存限制：受限于 SQL Server 的内存大小，可能不适合存储大量数据。 性能瓶颈：在大规模数据操作时，可能会成为性能瓶颈。 不支持某些特性：例如不支持索引、触发器等。
选择 对于简单的、一次性的查询操作，使用 CTE 可以提高代码的可读性和性能。 如果需要跨多个查询使用临时数据，并且需要索引或复杂的数据结构，使用临时表可能是更好的选择。 对于内存操作且数据量不大的情况，表变量提供了一种快速且简单的方式。然而，对于大规模数据处理，应该考虑使用其他方法如临时表或外部存储解决方案

 

 

 

 

-- 使用 WITH 子句创建临时表（CTE - Common Table Expressions）
--WITH 子句在 SQL Server 中通常用于定义公共表达式（Common Table Expressions, CTE），这是一种临时结果集，它只在定义它的查询中可见。CTE 通常用于简化复杂的查询，特别是在需要多次引用同一子查询结果时。虽然这不是传统意义上的“临时表”，但它可以模拟临时表的行为，尤其是在只用于单个查询的情况下。

CREATE PROCEDURE CTEExample
AS
BEGIN
    -- 使用WITH CTE
    WITH TempCTE AS (
        SELECT ID, Name FROM SomeTable WHERE SomeCondition = 1
    )
    SELECT * FROM TempCTE;
END;

 

临时表

 创建临时表
使用 CREATE TABLE #表名： 灵活性高，可以建表时定义复杂的结构（例如，包含多个列和约束），并且可以多次使用这个表进行操作

  -- CREATE  PROCEDURE SP_SELECT_DATE  @StartDate date = '2025-01-01' , @EndDate date = '2025-12-31'
 ALTER  PROCEDURE SP_SELECT_DATE  @StartDate date = '2025-01-01' , @EndDate date = '2025-12-31'
as 
  ---创建表，并把数据取下来
    CREATE TABLE #Tb1
    (
            ID NVARCHAR(100),
            minTime DATETIME,
            maxTime DATETIME
    );

    DECLARE @MiddleDate DATE = @StartDate;

    DECLARE @StartDatefor1 DATETIME2(7), @endDatefor1 DATETIME2(7); 

     WHILE @MiddleDate <= @EndDate  
     BEGIN
        SET @StartDatefor1 = CAST(          CAST(@MiddleDate AS VARCHAR(10)) + ' 09:00:00'           AS DATETIME2(7)    ); 
        SET @endDatefor1 = DATEADD(SECOND, -1,
            CAST(       CAST(  DATEADD(DAY, 0, @MiddleDate) AS VARCHAR(10) ) + ' 18:00:00'          AS DATETIME2(7)  )
         
         );

        insert into #Tb1 (ID,minTime,maxTime) values (@MiddleDate,@StartDatefor1,@endDatefor1)
        
        SELECT @MiddleDate=DATEADD(DAY, 1, @MiddleDate)
     END

    SELECT * FROM #Tb1
     ---删除表
    DROP TABLE #Tb1
  ;
    
   -- 执行存储过程
 EXEC SP_SELECT_DATE  '2024-05-01' ,'2025-05-01' ;

 

表变量

-- 表变量 
DECLARE @TempEmployees TABLE (id INT, name VARCHAR(100));

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

SELECT * FROM @TempEmployees;