您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 實用技巧日積月累：日期、分頁、轉換、觸發器

編輯：關於SqlServer

SQL Server 開發維護實用技巧彙總

收集整理實務開發中常用的 T-SQL 技巧，涵蓋日期時間操作、分頁查詢、Oracle 相容函數、時間格式化、觸發器、Identity 序列等高頻場景。

1. 字串轉日期時間

-- 標準 ISO 格式 (最安全，不受語言/日期格式設定影響)
SELECT CAST('2024-01-15' AS DATETIME);           -- 2024-01-15 00:00:00.000
SELECT CAST('2024-01-15T14:30:00' AS DATETIME2); -- 2024-01-15 14:30:00.0000000

-- CONVERT 指定風格代碼
SELECT CONVERT(DATETIME, '2024/01/15', 111);     -- yyyy/mm/dd
SELECT CONVERT(DATETIME, '15/01/2024', 103);     -- dd/mm/yyyy
SELECT CONVERT(DATETIME, '01/15/2024', 101);     -- mm/dd/yyyy

2. 日期加減運算

-- DATEADD: 日期加減
SELECT DATEADD(DAY, 1, GETDATE());      -- 明天
SELECT DATEADD(MONTH, -3, GETDATE());   -- 3 個月前
SELECT DATEADD(YEAR, 1, '2024-01-15');  -- 2025-01-15

-- DATEDIFF: 日期差值
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE());  -- 今年經過天數
SELECT DATEDIFF(SECOND, StartTime, EndTime) AS DurationSec;

3. 綜合日期運算範例

-- 昨天日期
SELECT DATEADD(DAY, -1, CAST('2024-01-15' AS DATE));

-- 本月第一天
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

-- 本月最後一天
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0));

-- 今年第一天
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0);

4. SQL Server 分頁查詢 (三種主流寫法)

-- 方法 1: OFFSET/FETCH (SQL Server 2012+ 標準、效能最佳)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- 第 3 頁，每頁 10 筆

-- 方法 2: ROW_NUMBER() 子查詢 (2005-2008 相容)
WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ProductID) AS RN
    FROM Products
)
SELECT * FROM Numbered WHERE RN BETWEEN 21 AND 30;

-- 方法 3: TOP + NOT IN (舊版相容、大資料量效能差)
SELECT TOP 10 * FROM Products
WHERE ProductID NOT IN (
    SELECT TOP 20 ProductID FROM Products ORDER BY ProductID
)
ORDER BY ProductID;

5. 實現 Oracle DECODE 功能

-- SQL Server 使用 CASE 運算式替代
SELECT 
    MAPPING_ID,
    MAX(CASE WIDGET_TYPE WHEN 2 THEN INSTANCE_NAME END) AS source_name,
    MAX(CASE WIDGET_TYPE WHEN 1 THEN INSTANCE_NAME END) AS target_name
FROM OPB_WIDGET_INST
WHERE MAPPING_ID = 2
GROUP BY MAPPING_ID;

-- 簡易 DECODE 邏輯
CASE @Value 
    WHEN 1 THEN 'One' 
    WHEN 2 THEN 'Two' 
    ELSE 'Other' 
END

-- 等同 Oracle: DECODE(@Value, 1, 'One', 2, 'Two', 'Other')

6. 日期時間格式化輸出字串

-- CONVERT 風格代碼常用對照
SELECT CONVERT(VARCHAR, GETDATE(), 120);  -- 2024-01-15 14:30:25 (yyyy-mm-dd hh:mi:ss 24h)
SELECT CONVERT(VARCHAR, GETDATE(), 121);  -- 2024-01-15 14:30:25.123 (含毫秒)
SELECT CONVERT(VARCHAR, GETDATE(), 112);  -- 20240115 (yyyymmdd)
SELECT CONVERT(VARCHAR, GETDATE(), 108);  -- 14:30:25 (hh:mi:ss)

-- 只取時分秒
SELECT SUBSTRING(CONVERT(VARCHAR, GETDATE(), 120), 12, 8);  -- 14:30:25
SELECT CONVERT(TIME, GETDATE());  -- 2008+ 專用 TIME 型別

-- FORMAT 彈性格式化 (2012+)
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss', 'zh-TW');
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日', 'zh-TW');

7. Oracle 分區查詢語法對照

-- Oracle 語法 (PARTITION 子句在表名之後)
SELECT * FROM BILL.T_USAGE_EVENTS_20070701 PARTITION (LATN534);

-- SQL Server 分區表查詢 (自動分區消除，無需指定分區)
-- 建立分區函數/配置/表
CREATE PARTITION FUNCTION pf_Yearly (DATE) AS RANGE RIGHT FOR VALUES 
('2024-01-01', '2025-01-01', '2026-01-01');
CREATE PARTITION SCHEME ps_Yearly AS PARTITION pf_Yearly ALL TO ([PRIMARY]);
CREATE TABLE Sales (SaleDate DATE, Amount MONEY) ON ps_Yearly(SaleDate);

-- 查詢自動只掃描相關分區
SELECT * FROM Sales WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01';

8. 觸發器建立與修改

-- 建立 DML 觸發器
CREATE TRIGGER trg_Part1_Delete
ON dbo.Part1
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PartMapping (Col1, Col2, Col3) VALUES (2, 9, 3);
END;

-- 修改觸發器 (ALTER TRIGGER)
ALTER TRIGGER trg_Part1_Delete
ON dbo.Part1
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PartMapping (Col1, Col2, Col3) VALUES (2, 9, 3);
END;

-- 重要限制：觸發器不支援 TRUNCATE TABLE 操作
-- TRUNCATE 不會觸發 AFTER DELETE 觸發器

9. Inserted/Deleted 虛擬表 (觸發器核心)

-- INSERT 觸發器：Inserted 有新資料，Deleted 為空
-- DELETE 觸發器：Deleted 有舊資料，Inserted 為空
-- UPDATE 觸發器：Deleted 有舊值，Inserted 有新值

-- 範例：稽核追蹤觸發器
CREATE TRIGGER trg_Orders_Audit
ON dbo.Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO OrderAudit (OrderID, ActionType, ChangedBy, ChangedAt, OldData, NewData)
    SELECT 
        COALESCE(i.OrderID, d.OrderID),
        CASE 
            WHEN i.OrderID IS NOT NULL AND d.OrderID IS NOT NULL THEN 'UPDATE'
            WHEN i.OrderID IS NOT NULL THEN 'INSERT'
            ELSE 'DELETE'
        END,
        SYSTEM_USER,
        GETDATE(),
        (SELECT * FROM Deleted d2 WHERE d2.OrderID = COALESCE(i.OrderID, d.OrderID) FOR JSON AUTO),
        (SELECT * FROM Inserted i2 WHERE i2.OrderID = COALESCE(i.OrderID, d.OrderID) FOR JSON AUTO)
    FROM Inserted i
    FULL JOIN Deleted d ON i.OrderID = d.OrderID;
END;

10. Identity 序列號 (對應 Oracle SEQUENCE)

-- 建立表時指定 Identity
CREATE TABLE dbo.TestIdentity (
    ID INT IDENTITY(1,1) PRIMARY KEY,  -- 種子 1，遞增 1
    Name NVARCHAR(50)
);

-- 插入時不指定 Identity 欄位
INSERT INTO dbo.TestIdentity (Name) VALUES ('Item A');  -- ID 自動產生 1
INSERT INTO dbo.TestIdentity (Name) VALUES ('Item B');  -- ID 自動產生 2

-- 手動插入 Identity 值 (需開啟 IDENTITY_INSERT)
SET IDENTITY_INSERT dbo.TestIdentity ON;
INSERT INTO dbo.TestIdentity (ID, Name) VALUES (100, 'Manual Insert');
SET IDENTITY_INSERT dbo.TestIdentity OFF;

-- 重置 Identity 種子
DBCC CHECKIDENT ('dbo.TestIdentity', RESEED, 100);  -- 下一筆從 101 開始

11. 交易隔離與回滾模擬 (Oracle SAVEPOINT 對應)

-- SQL Server 無 SAVEPOINT，使用巢狀交易模擬
BEGIN TRANSACTION OuterTran;
    INSERT INTO Table1 VALUES (1);
    
    BEGIN TRANSACTION InnerTran;  -- 實際上只是標記點
        INSERT INTO Table2 VALUES (2);
        -- 發生錯誤
        ROLLBACK TRANSACTION InnerTran;  -- 只回滾到 InnerTran 標記
        -- 但 SQL Server 巢狀交易只有最外層 COMMIT 才真正提交
    -- 正確做法：使用 @@TRANCOUNT 判斷
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
COMMIT TRANSACTION;