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

SQL Server 字串搜尋效能比較：CHARINDEX vs PATINDEX vs LIKE 實測分析

編輯：SQL Server 2005

測試環境

SQL Server 2008（結構相容 2005+）
測試表：15,000 列，主鍵 CHAR，無其他索引
搜尋欄位：NVARCHAR(MAX) / NTEXT，最大長度 12,000 字元
搜尋字串：'192.168.70.236'（固定 IP 字串）

三種寫法

-- 1. LIKE
SELECT * FROM ProductTemp WHERE ProductDesc LIKE '%192.168.70.236%';

-- 2. CHARINDEX
SELECT * FROM ProductTemp WHERE CHARINDEX('192.168.70.236', ProductDesc) > 0;

-- 3. PATINDEX
SELECT * FROM ProductTemp WHERE PATINDEX('%192.168.70.236%', ProductDesc) > 0;

效能測試結果（平均耗時）

方法	相對耗時	備註
CHARINDEX	1.00 (基準)	最快
LIKE	~1.20	次之
PATINDEX	~1.20+	最慢，差異約 20%

結論：固定字串尋 **CHARINDEX > LIKE > PATINDEX**。

適用性與限制差異

特性	CHARINDEX	PATINDEX	LIKE
支援萬用字元	❌ 僅固定字串	✅ 完整 pattern（%、_、[]）	✅ 完整 pattern
NTEXT/TEXT 位移限制	⚠️ 起始位置 > 3987 字元傳回 0（SQL 2008）	✅ 無限制	✅ 無限制
回傳值	起始位置 (INT)，找不到傳 0	起始位置 (INT)，找不到傳 0	Boolean
大小寫敏感度	依定序	依定序	依定序

實務建議

固定字串、效能優先 → CHARINDEX（注意 NTEXT 位移限制，建議遷移至 NVARCHAR(MAX)）。
需模糊比對、Regular Expression-like → PATINDEX 或 LIKE。
單純存在性檢查 → LIKE 可讀性最佳。
大型 LOB 欄位：務必遷移至 VARCHAR(MAX)/NVARCHAR(MAX)，移除 8KB 頁面行外儲存限制，並啟用全文檢索獲得最佳效能。

補充：全文檢索替代方案

-- 建立全文檢索目錄與索引
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON ProductTemp(ProductDesc) KEY INDEX PK_ProductTemp ON ftCatalog;

-- 查詢
SELECT * FROM ProductTemp 
WHERE CONTAINS(ProductDesc, ' "192.168.70.236" ');

全文檢索對大型文字欄位提供 **反向索引**，查詢速度比任何 T-SQL 字串函數快數量級。