萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 常用知識點備忘錄:執行順序、存儲程序模板、分組 Top-N、表值參數

P1:SQL 執行邏輯順序 (關鍵優化依據)

(1) FROM / JOIN          -- 產生虛擬表 VT1
(2) ON                   -- 篩選 JOIN 條件 → VT2
(3) OUTER JOIN           -- 加回外部列 → VT3
(4) WHERE                -- 列篩選 → VT4
(5) GROUP BY             -- 分組 → VT5
(6) CUBE | ROLLUP        -- 超級群組 → VT6
(7) HAVING               -- 群組篩選 → VT7
(8) SELECT               -- 投影 → VT8
(9) DISTINCT             -- 去重 → VT9
(10) ORDER BY            -- 排序 → VC10 (游標)
(11) TOP / OFFSET-FETCH  -- 取前 N 列 → VT11 (最終結果)

啟示:WHERE 在 GROUP BY 前執行,故無法用別名;SELECT 最後才投影,故 ORDER BY 可用別名。

P2:帶 TRY...CATCH 的儲存程序模板

USE [DB]; GO
SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; GO

CREATE PROCEDURE [dbo].[UP_InsertData]
    @CustomerName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Now DATETIME = GETDATE();

    BEGIN TRY
        -- 業務 SQL 寫這裡
        INSERT INTO Customers (Name, CreateTime) VALUES (@CustomerName, @Now);
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrorSeverity INT = ERROR_SEVERITY(),
                @ErrorState INT = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

P3:帶交易控制的儲存程序模板

CREATE PROCEDURE [dbo].[UP_InsertWithTx]
    @CustomerName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Now DATETIME = GETDATE();

    BEGIN TRY
        BEGIN TRANSACTION myTrans;
        -- 業務 SQL
        INSERT INTO Customers (Name, CreateTime) VALUES (@CustomerName, @Now);
        -- 可多條 DML
        COMMIT TRANSACTION myTrans;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION myTrans;
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrorSeverity INT = ERROR_SEVERITY(),
                @ErrorState INT = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END

P8:分組取每組前 N 筆

SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY ProductNO ORDER BY ProductNO) AS RowNum,
        *
    FROM IM.dbo.ItemInfo
) t
WHERE t.RowNum IN (1, 2, 3);  -- 每組前 3 筆

P9:使用者自訂表類型 (Table-Valued Parameters, TVP)

建立型別

CREATE TYPE [dbo].[GCRP_PendingGiftCard_TYPE] AS TABLE (
    [RowNum] INT NULL,
    [GiftCardNO] VARCHAR(500) NULL,
    [UsedDate] DATETIME NULL,
    [CustomerName] VARCHAR(50) NULL,
    [ReduceAmount] DECIMAL(18,2) NULL,
    [Amount] DECIMAL(18,2) NULL
);

應用層呼叫

// C# 範例
var dt = new DataTable();
dt.Columns.Add("RowNum", typeof(int));
dt.Columns.Add("GiftCardNO", typeof(string));
// ... 填充資料

using (var cmd = new SqlCommand("UP_ProcessGiftCards", conn)) {
    cmd.CommandType = CommandType.StoredProcedure;
    var param = cmd.Parameters.AddWithValue("@TVP", dt);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.GCRP_PendingGiftCard_TYPE";
    cmd.ExecuteNonQuery();
}

儲存程序接收

CREATE PROCEDURE UP_ProcessGiftCards
    @TVP GCRP_PendingGiftCard_TYPE READONLY
AS
BEGIN
    SELECT * FROM @TVP;  -- 如同操作表變數
    -- 可 INSERT INTO TargetTable SELECT * FROM @TVP;
END

TVP 優勢

  • 單次往返傳遞批次資料,減少網路往返
  • 伺服器端可直接 JOIN、篩選、聚合,效能優於迴圈呼叫
  • 型別安全,編譯期檢查結構
  • READONLY 關鍵字強制 (TVP 僅輸入)

總結

掌握執行順序優化查詢、標準化程序模板保證品質、善用窗口函數解決 Top-N、採用 TVP 高效傳遞批次資料,是 T-SQL 開發進階的四大基石。

copyright © 萬盛學電腦網 all rights reserved