NHibernate 2.0.1 實現 SQL Server 2000 真分頁：自訂 Dialect 完整攻略

痛點：內建 Dialect 的效能災難

NHibernate 內建 MsSql2000Dialect 的 GetLimitString 僅支援 TOP N（無 Offset），對大資料量分頁效能極差：

public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
    if (offset > 0)
        throw new NotSupportedException("SQL Server does not support an offset");
    // 僅支援 TOP limit，無法真正分頁
    return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + limit);
}

解決方案：繼承並重寫 Dialect

建立 MySqlServer2000Dialect 繼承 MsSql2000Dialect，啟用 SupportsLimitOffset 並實作高效分頁邏輯（使用暫存表 + ROWCOUNT）。

完整自訂 Dialect 程式碼

public class MySqlServer2000Dialect : MsSql2000Dialect
{
    // 輔助：取得 FROM 子句位置
    private static int GetFromIndex(SqlString querySqlString)
    {
        string subselect = querySqlString.GetSubselectString().ToString();
        int fromIndex = querySqlString.IndexOfCaseInsensitive(subselect);
        if (fromIndex == -1)
            fromIndex = querySqlString.ToString().ToLowerInvariant().IndexOf(subselect.ToLowerInvariant());
        return fromIndex;
    }

    // 移除 ORDER BY 結尾的 ASC/DESC
    private static string RemoveSortOrderDirection(string sortExpression)
    {
        return System.Text.RegularExpressions.Regex.Replace(sortExpression.Trim(), @"(\)|\s)(?i:asc|desc)$", "$1").Trim();
    }

    // 關鍵：宣告支援 Offset
    public override bool SupportsLimitOffset
    {
        get { return true; }
    }

    // 核心分頁邏輯
    public override NHibernate.SqlCommand.SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
    {
        if (offset == 0)
            return base.GetLimitString(querySqlString, offset, limit); // 無 Offset 走原生 TOP

        // 統一轉小寫處理
        SqlString myQuery = new SqlString(RemoveSortOrderDirection(querySqlString.ToString().ToLower()));

        int orderIndex = myQuery.LastIndexOfCaseInsensitive(" order by ");
        if (orderIndex <= 0)
            throw new NotSupportedException("must specify 'order by' statement to support limit operation with offset in SqlServer2000");

        string orderBy = myQuery.Substring(orderIndex).ToString().Trim();
        string[] sortExpressions = myQuery.Substring(orderIndex).ToString().Trim().Substring(9).Split(',');

        int fromIndex = GetFromIndex(myQuery);
        SqlString from = myQuery.Substring(fromIndex, orderIndex - fromIndex).Trim();

        // 擷取主資料表別名（假設 FROM 後第一個空格後為別名）
        string fromAsName = from.ToString().Substring(from.ToString().IndexOf(' ', 5)).Trim();

        SqlString select = myQuery.Substring(0, fromIndex);
        int pageSize = limit - offset;

        SqlStringBuilder result = new SqlStringBuilder();

        // 核心：使用暫存表 @indextable 模擬 ROW_NUMBER
        result.Add("declare @indextable table(id int identity(1,1),nid int) set rowcount " + limit);
        result.Add(" insert into @indextable(nid) select");
        result.Add(" " + RemoveSortOrderDirection(sortExpressions[0]).Replace(fromAsName, "t") + " " + from.ToString().Replace(fromAsName, "t") + " " + orderBy.Replace(fromAsName, "t") + " desc ");
        result.Add(" " + select.ToString() + " " + from.ToString() + ",@indextable a where " + RemoveSortOrderDirection(sortExpressions[0]) + " = a.nid and a.[id] between " + (offset + 1) + " and " + limit);

        return result.ToSqlString();
    }
}

原理深度解析

1. 暫存表 @indextable：含 IDENTITY(1,1) 欄位自動產生行號。
2. SET ROWCOUNT：限制插入暫存表的筆數為 limit（Offset + PageSize）。
3. 倒序插入：ORDER BY ... DESC 使最新資料先獲得較小的行號。
4. 主查詢 JOIN 暫存表：依排序鍵值 nid 關聯，再以 a.[id] BETWEEN offset+1 AND limit 取出目標頁。

使用前提（重要）

• HQL 必須包含 ORDER BY，且排序欄位需為主鍵。
• 主鍵必須為自動遞增，確保排序確定性。
• 若排序非主鍵，需修改 RemoveSortOrderDirection 邏輯以支援多欄位排序。

組態啟用：hibernate.cfg.xml

<property name="dialect">
    YourNamespace.MySqlServer2000Dialect, YourAssembly
</property>

替換原有 NHibernate.Dialect.MsSql2000Dialect 即可。

效能對比

遷移建議：升級至 SQL Server 2005+

若可升級資料庫，強烈建議改用 MsSql2005Dialect 或 MsSql2012Dialect，原生支援 ROW_NUMBER() OVER()，語法更簡潔、效能更穩定。

結語

透過繼承 MsSql2000Dialect 並實作 GetLimitString，即可在 NHibernate 2.0.1 中為 SQL Server 2000 帶來生產級的真分頁能力。核心在於「暫存表 + IDENTITY + ROWCOUNT」經典模式，若專案仍受限於 SQL 2000，此方案為最佳選擇。