SQL 字串欄位部分值篩選：CHARINDEX vs IN vs 動態 SQL 效能與寫法對比

情境：卡口編號 (KKBH) 字典值域 {01,02,03...}，使用者勾選多個卡口查詢記錄

方案一：動態 IN 列表

-- 應用層組裝
WHERE KKBH IN ('01','02','05')

• 優點：語意清晰、優化器可使用索引 Seek
• 缺點：參數化困難、IN 列表過長（> 1000）可能產生效能問題、動態 SQL 注入風險需防範

方案二：CHARINDEX 搜尋分隔字串

-- 應用層組裝分隔字串：'01@02@05'
-- SQL 參數化
DECLARE @Filter VARCHAR(MAX) = '01@02@05';

SELECT * FROM TrafficRecord
WHERE CHARINDEX('@' + KKBH + '@', '@' + @Filter + '@') > 0;

• 關鍵技巧：前後加分隔符，避免 '01' 誤匹配 '011'
• 優點：單一參數、無動態 SQL、列表長度無上限
• 缺點：無法使用索引 Seek（掃描全表/索引）、大表效能差

方案三：表值參數 (TVP) — SQL 2008+ 最佳實踐

-- 1. 建立使用者定義表類型
CREATE TYPE dbo.KKBHList AS TABLE (KKBH VARCHAR(10) PRIMARY KEY);

-- 2. 應用層填充 DataTable 傳入
-- 3. SQL 端
DECLARE @Selected KKBHList;
INSERT INTO @Selected VALUES ('01'),('02'),('05');

SELECT t.* FROM TrafficRecord t
INNER JOIN @Selected s ON t.KKBH = s.KKBH;

• 優點：可使用索引 Seek、強型別、參數化、無注入風險、效能最佳
• 缺點：需建立類型、.NET 需用 SqlParameter.TypeName 傳遞 DataTable/IEnumerable

方案四：JSON 陣列參數 — SQL 2016+

DECLARE @Json NVARCHAR(MAX) = N'["01","02","05"]';

SELECT t.* FROM TrafficRecord t
WHERE EXISTS (
    SELECT 1 FROM OPENJSON(@Json) j
    WHERE j.value = t.KKBH
);

• 優點：無需建立類型、原生 JSON 支援、參數化
• 缺點：SQL 2016+ 才支援、大陣列效能略遜 TVP

效能實測對比（百萬筆資料、索引於 KKBH、篩選 10 個值）

選型建議

1. TVP：.NET 應用、高頻查詢、大表、需最佳效能
2. JSON：SQL 2016+、非 .NET 應用、中等頻率
3. 動態 IN：簡單查詢、值列表 < 50、無 TVP/JSON 支援環境
4. CHARINDEX：僅限小表/臨時表/一次性查詢，嚴禁大表生產使用

UI 實作提示

• ListBox 雙欄移動 / CheckBoxList / 下拉多選
• 前端收集選值 → 組裝 TVP DataTable / JSON 陣列 / 逗號字串
• 後端統一使用參數化查詢，拒絕字串拼湊 SQL