[T-SQL] Temporary Tables

• Temporary Table(臨時表)

• 適用於複雜或重覆的子查詢，如果有 1,000 萬筆的資料表，而只需要其中的 1萬筆，並且需重覆的篩選、比對 (Join) 這 1萬筆資料，Temporary Table 能減少重複查詢這龐大資料次數，可讓資料篩選與比對變得更迅速
• CASE 1 (Temporary Table)

Create Table #TempTable ( Name varchar(50), Age int )

• 建立一個區域性的暫存資料表 (Local) 
• 存在 tempdb 資料庫中，建立一個名稱為 #TempTabel 的資料表 (位於暫存資料表中)
• 這個暫存資料表只有建立者可以使用，其他人可以看到，但無法存取
• 利用 DROP TABLE 來明確卸除暫存資料表，否則當建立該暫存資料表的連線結束時，SQL Server 會自動將其刪除
• CASE 2 (Table Variables - SQL Server 2000)

DECLARE @TmpTable TABLE ( Name varchar(50), Age int )

• 建立一個 TABLE 資料型別的暫存資料表
• 完全存在於記憶體中，且不會存在於 tempdb，因此其他人無法看到
• 當定義 TABLE 資料型別的函數、預存程序或批次結束時，就會自動清除這個暫存資料表，不須手動去 DROP
• 主要使用大多為 CASE 1 (Temporary Table) 方法，因使用上比較方便，Temporary Table需知下列資訊：
• TABLE NAME 前加入 「#」，表示這是一個 Temporary Table
• Session 關閉時，這個 TABLE 將會自動  DROP
• Temporary Table 使用完畢後, 下指令去 DROP，而不是讓系統自動回收
• Temporary Table 存在主機記憶體中，因此存取速度較快
• Temporary Table 存在於 tempdb 這個 database 裡 
• 如果有兩個使用者建立同一個名字的 Temporary Table，則他們會各自擁有獨立的一份，互相不會干擾
• 若 stored procedure A 建立了一個 Temporary Table，並呼叫 stored procedure B，則在 B 中可以存取這個 Temporary Table
• 如果 SQL Server Management Studio or Query Analyzer 中建立的 Temporary Table，會等到手動 DROP 去關閉 Session 才會消失
• Temporary Table Code

USE [LyndonDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Lyndon
-- Create date: 2017-08-23
-- =============================================
ALTER PROCEDURE [dbo].[SP_TemporaryTable](
  @MinAge int,
  @MaxAge int
)
AS

BEGIN
-- CREATE TemporaryTable
SELECT 
    Name,
    Age
    INTO #temp
FROM tblUserInfo
WHERE (Age BETWEEN @MinAge AND @MaxAge)

-- USE TemporaryTable
SELECT AVG(Age) AS AVGAge
FROM #temp 

-- DROP TemporaryTable
DROP TABLE #temp
END
 

• Temporary Table / Table Variables 使用時機

• 當暫存的資料筆數小於100筆，使用 Table Variables，否則使用 Temporary Table，因為針對 Table Variables，SQL Server 不會去解析/最佳化它的效能
• 當我們須要對表格建立索引(Index)時，則必須使用 Temporary Table 
• 使用 Temporary Table 時，最好能在建立後並建立索引，這能增加效能 (SQL Server 2005後，這方面已改善，所以可以不建索引，但建立它仍是一個好習慣)

• Global Temporary Tables (全域暫存表) 

• 在表格名字前面，加入兩個「##」(如：##gtemp)，則表示這是個全域暫存表，也就是說，這個表格和一般表格一樣，可以被所有連線 (connections/sessions) 使用，在SQL Server 中，這樣的應用並不多見

• REF:

• SQL - Temp Table 小技巧
• 暫存表(Temporary Tables)的使用簡介
• 建立#TempTable與Declare @TempTable有何差別
• Temporary Tables