SQL Server 2005 CLR 實作：自訂全文檢索索引 (山寨版)

本文嘗試以 CLR 實作一個簡易的「反向索引」機制，透過檔案系統目錄結構模擬索引儲存，支援精確 LIKE '%關鍵字%' 模式的快速查詢。此為概念驗證 (PoC) 性質，非生產環境建議方案，但思路可供參考擴充。

索引原理

• 建索引：將索引內容以 Unicode 編碼，取連續兩字元 (4 bytes) 作為四層目錄路徑；主鍵值為檔名，字元位置為副檔名。檔案本身 0 bytes，不儲存資料。
• 查詢：搜尋關鍵字前 4 bytes 對應目錄下的所有檔案，依副檔名遞推檢查後續字元目錄是否存在同主鍵檔案，最終回傳符合主鍵集合。

範例：索引「山寨索引」四字

Unicode bytes: [113,92, 232,91, 34,125, 21,95]
目錄結構:
../113/92/232/91/pk001.0
../232/91/34/125/pk001.1
../34/125/21/95/pk001.2

核心 CLR 程式碼精簡版 (C#)

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    private static string _root = @"D:/Index";

    [SqlFunction] public static SqlBoolean SetRoot(SqlString path) { ... }
    [SqlFunction] public static SqlString GetRoot() { ... }

    [SqlFunction] public static SqlInt32 SetIndex(SqlString key, SqlString indexName, SqlString content) { ... }

    [SqlFunction(TableDefinition = "pk NVARCHAR(900)", FillRowMethodName = "FillRow")]
    public static IEnumerable GetIndex(SqlString word, SqlString indexName) { ... }
    public static void FillRow(object obj, out SqlString pk) { ... }

    // 內部邏輯：_setIndex 建立多層目錄與 0-byte 檔案
    //           _getIndex 依關鍵字 bytes 遍歷目錄並交集比對主鍵
}

部署與使用範例

-- 設定索引根目錄
SELECT dbo.xfn_SetMyIndexFileRoot('D:/MyIndex');

-- 建立測試表與資料
CREATE TABLE test(id UNIQUEIDENTIFIER, name NVARCHAR(100), caption NVARCHAR(100));
INSERT INTO test SELECT TOP 3 NEWID(), N'我的索引', N'測試' FROM sysobjects;
INSERT INTO test SELECT TOP 3 NEWID(), N'我的測試', N'索引' FROM sysobjects;

-- 建立索引
DECLARE @t INT;
SELECT @t = dbo.xfn_SetKeyForMyIndex(id, 'testIndex', name + N' ' + caption) FROM test;

-- 查詢包含「測試 索引 我的」的記錄
SELECT a.* FROM test a
JOIN dbo.xfn_GetKeyFromMyIndex(N'測試 索引 我的', 'testIndex') b ON a.id = b.pk;

限制與風險

• 僅支援精確子字串比對，不支援分詞、同義詞、相關度排序。
• 海量資料重建索引時 CPU/IO 壓力極大，單一目錄檔案過多會嚴重降低 NTFS 效能。
• 需自行處理並發、交易一致性、備份還原等問題。
• 建議改用 SQL Server 內建 Full-Text Search 或 Semantic Search，或整合 Elasticsearch/Solr 等專業搜尋引擎。