暫時資料庫的用法 - Temp Table, WITH...AS

Temp Table
如果有時候會有需要臨時用到一個地方儲存資料的地方。
例如儲存未登入前的使用者習慣，直到登入後才寫入紀錄資料庫。
這時候就可以利用Temp Table來儲存資料庫。
Temp Table 全名叫做 Temporary Table
使用方法就是跟一般建立資料庫方法一樣，惟前面加上 "#" 字號
在連接 (Connection) 區間結束時，系統會自動將資料庫 Drop 掉。
不過在寫程式習慣上，還是希望在使用完畢使用 Drop 指令。

它的特點

1. 儲存在主機記憶體中，存取速度較快，卻也耗費記憶體空間，不建議在大量使用上
2. 如果有兩筆建立連線都使用一樣的名字，但彼此並不會互相干擾。
3. 如果在預存程序中，會在建立的預存程序結束時才會自動 Drop 掉該暫存資料表，也就是中間如果有呼叫其他預存程序，其他預存程序也可以使用該暫存資料表。

• 建立 Temp Table

CREATE TABLE #TempTableName (
    ID int IDENTITY(1,1) NOT NULL,
    Column1 varchar(30) NULL,
    Column2 varchar(20) NOT NULL,
    CreateTime datetime NOT NULL
 )


• 刪除 Temp Table

DROP TABLE #TempTableName;



WITH...AS
全名為 Common Table Expression (CTE)
這個功能是 SQL 2005 以上版本才開始支援的語法。
如果使用所需資料為資料庫內資料，為拿出部分或是JOIN等處理後的暫存資料庫。

這是使用次數為一次性的暫時性查詢(更新、刪除)方法。
主要是輔以查詢範圍的資料利用，包括JOIN後的資料更新等等...
在連接 (Connection) 區間，僅能查詢(更新、刪除)一次。

它的特點

1. 前面可不加 # 表示暫存資料表
2. 查詢或更新只能使用一次，之後便不可再進行資料的利用。
3. 可針對定義內的資料庫進行查詢、更新、刪除等指令，惟不可進行新增的動作
4. 一旦更新、刪除後，隨即會影響原本的資料表

• 使用語法

WITH TempTableName
AS
(
        SELECT Command
)
SELECT/UPDATE/DELETE Command


範例
建立兩張資料表如下兩張圖
TableName1

TableName2

各試著鍵入一些資料後，可利用WITH進行基本的查詢

WITH Temp1
AS (
SELECT     A.ID, A.Column1, A.Column2, A.CreateTime, B.TableName2ID, B.Column3, B.Column4
FROM         TableName1 AS A INNER JOIN
TableName2 AS B ON A.ID = B.TableName2ID )
SELECT * FROM Temp1


搜尋結果

可進行資料的更新

WITH Temp1
AS (
SELECT     A.ID, A.Column1, A.Column2, A.CreateTime, B.TableName2ID, B.Column3, B.Column4
FROM         TableName1 AS A INNER JOIN
TableName2 AS B ON A.ID = B.TableName2ID )
UPDATE Temp1 SET Column1 = '1234' WHERE TableName2ID = 2

執行結果

而原本的資料表資料也隨即更新成新的數值

兩種暫存資料庫的使用方式，可依照特點去暫存使用資料。