~楓花雪岳~: [SQL] TempDB

星期五, 4月 25, 2014

[SQL] TempDB

TempDB 顧名思義就是暫時性資料庫，存放系統自動建立或使用者建立的物件。

TempDB 的 Database_ID 是 2

SELECT DB_ID('TempDB') AS Database_ID

TempDB 中建立區域物件、全域物件 - 以 Table 說明

Table 名稱前有 # 代表區域 Table、## 代表全域 Table，區域 Table 只有建立它的 session 可以存取，全域 Table 則是全部的 session 都可以存取。

CREATE TABLE #Local(ID int)
GO
CREATE TABLE ##Global(ID int)
GO

在 T-SQL 經過複雜運算後，可以把結果存在暫存 Table 內，方便之後再使用，但會建議執行完畢後，把暫存 Table 刪除

IF OBJECT_ID('TempDB..#Local') IS NOT NULL
    DROP TABLE #Local

IF OBJECT_ID('TempDB..##Global') IS NOT NULL
    DROP TABLE ##Global

每次啟動 SQL Server 時會重新建立 TempDB

重新啟動 SQL Server，TempDB 中全部的資料會被永久刪除，而 SQL Server 建立 TempDB 是根據 Model DB 內的資料來建立，假如我們需要在 TempDB 內永遠存在一個資料表，可以在 Model DB 內建立該 Table，當 SQL Server 啟動時，TempDB 內就會存在該 Table。

在 Model DB 中建立 Demo Table

USE model
GO
CREATE TABLE Demo(ID INT)
GO

從下圖可以看見 Model DB 中有 Demo Table，而 TempDB 中是不存在的

重新啟動 SQL Server 來重建 TempDB 後，TempDB 中就可以看見 Demo Table

SQL Server 是根據 Model DB 來建立 TempD，萬一 Model DB毀損，可能會造成無法建立 TempDB，導致 SQL Server 無法啟動。

另外可以利用 TempDB 會被重建此點來查詢 SQL Server 上次啟動時間點。

SELECT 
      name , 
      crdate -- 上次啟動時間
FROM sysdatabases 
WHERE name = 'tempdb'

TempDB 限制（MSDN 資料）

加入檔案群組。

備份或還原資料庫。

變更定序。預設定序是伺服器定序。

變更資料庫擁有者。 tempdb 是歸 dbo 所擁有。

建立資料庫快照集。

卸除資料庫。

從資料庫卸除 guest 使用者。

啟用異動資料擷取。

參與資料庫鏡像。

移除主要檔案群組、主要資料檔或記錄檔。

重新命名資料庫或主要檔案群組。

執行 DBCC CHECKALLOC。

執行 DBCC CHECKCATALOG。

將資料庫設定為 OFFLINE。

將資料庫或主要檔案群組設定為 READ_ONLY。