撰寫可重複執行 SQL 腳本的最佳實務：IF EXISTS 守護生產環境安全

為什麼需要冪等腳本？

生產環境部署腳本常面臨重複執行風險：首次執行失敗修正後重跑、多節點部署、CI/CD 管線重試。非冪等腳本會導致主鍵衝突、物件已存在報錯、資料重複，甚至中斷整個部署流程。

核心模式：IF NOT EXISTS / IF EXISTS 守衛

SQL Server 2005+ 透過目錄檢視判斷物件是否存在：

1. 插入基礎資料（防重複）

IF NOT EXISTS (SELECT 1 FROM SysConfig WHERE ConfigKey = 'AppVersion')
BEGIN
    INSERT INTO SysConfig (ConfigKey, ConfigValue) VALUES ('AppVersion', '1.0.0');
END

2. 建立資料表

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AuditLog' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.AuditLog (
        LogID BIGINT IDENTITY PRIMARY KEY,
        EventTime DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        EventType NVARCHAR(50) NOT NULL,
        Detail NVARCHAR(MAX)
    );
END

3. 新增/修改欄位

IF NOT EXISTS (SELECT 1 FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id
               WHERE t.name = 'Users' AND c.name = 'LastLoginIP')
BEGIN
    ALTER TABLE dbo.Users ADD LastLoginIP VARCHAR(45) NULL; -- IPv6 相容
END
ELSE
BEGIN
    -- 欄位已存在，若需調整型別可在此 ALTER COLUMN
    -- ALTER TABLE dbo.Users ALTER COLUMN LastLoginIP VARCHAR(45) NULL;
END

4. 重建檢視/預存程序/同義詞（DROP 再 CREATE）

-- 檢視
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'V_UserSummary' AND schema_id = SCHEMA_ID('dbo'))
    DROP VIEW dbo.V_UserSummary;
GO
CREATE VIEW dbo.V_UserSummary AS
SELECT UserID, UserName, COUNT(*) AS OrderCount FROM dbo.Users u LEFT JOIN dbo.Orders o ON u.UserID = o.UserID GROUP BY u.UserID, u.UserName;
GO

-- 預存程序
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'usp_GetUserOrders' AND schema_id = SCHEMA_ID('dbo'))
    DROP PROCEDURE dbo.usp_GetUserOrders;
GO
CREATE PROCEDURE dbo.usp_GetUserOrders @UserID INT AS
SELECT * FROM dbo.Orders WHERE UserID = @UserID ORDER BY OrderDate DESC;
GO

進階技巧

• 交易包裹：每個邏輯單元用 BEGIN TRY...BEGIN TRAN...COMMIT...END TRY BEGIN CATCH...ROLLBACK...THROW; END CATCH 確保原子性。
• 版本記錄表：建立 DeployLog(ScriptName, ExecutedAt, Success)，腳本首行檢查是否已執行成功，跳過重複執行。
• 資料遷移腳本：搭配 MERGE 實現 UPSERT，單一陳述式完成增量同步。

團隊規範建議

1. 所有上線腳本必須冪等，Code Review 關鍵檢查點。
2. 命名規範：V{版本}_{序號}_{描述}.sql（如 V2.3.1_001_Add_User_LastLoginIP.sql）。
3. 嚴禁在腳本中使用 USE [DBName]，由部署工具指定資料庫上下文。
4. 變更腳本不可刪除/修改歷史檔案，新增腳本累加演進。

結語

「能重複安全執行」是成熟資料庫變更管理的標誌。養成 IF EXISTS 守衛習慣，讓部署流程穩健、可追溯、可回滾。