SQL Server 分頁查詢演進與 ISNULL/COALESCE 函數深度對比

前言

本文涵蓋兩大核心主題：SQL Server 分頁技術從舊版 ROW_NUMBER 到新版 OFFSET-FETCH 的演進，以及 ISNULL 與 COALESCE 在資料類型處理、NULL 語意、效能等維度的深度對比。

分頁查詢技術演進

傳統 ROW_NUMBER 方案（SQL 2005/2008）

USE TSQL2012
GO
DECLARE @StartRow INT = 31, @EndRow INT = 40
SELECT [address], [city], [region] 
FROM (
    SELECT [SC].[address], [SC].[city], [SC].[region], 
           ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city], [SC].[custid]) AS RowNumber
    FROM Sales.Customers SC
) SaleCustomer 
WHERE RowNumber > @StartRow AND RowNumber < @EndRow
ORDER BY [address], [city], [region];

CTE 寫法（同樣基於 ROW_NUMBER）

WITH SaleCustomer AS (
    SELECT [SC].[address], [SC].[city], [SC].[region], 
           ROW_NUMBER() OVER(ORDER BY [SC].[address], [SC].[city], [SC].[custid]) AS RowNumber
    FROM Sales.Customers SC
)
SELECT [address], [city], [region] 
FROM SaleCustomer 
WHERE RowNumber > @StartRow AND RowNumber < @EndRow
ORDER BY [address], [city], [region]
GO

實測兩者執行計劃與效能無顯著差異，CTE 寫法可讀性較佳。

現代 OFFSET-FETCH 方案（SQL 2012+）

USE TSQL2012
GO
DECLARE @PageSize INT = 10, @PageIndex INT = 3
SELECT * FROM Sales.Customers 
ORDER BY custid 
OFFSET @PageIndex * @PageSize ROWS 
FETCH NEXT 10 ROWS ONLY
GO

優勢： 語法簡潔、符合 ANSI SQL 標準、無需子查詢、優化器支援更好。若專案最低需求 SQL 2012，強烈推薦使用 OFFSET-FETCH。

ISNULL vs COALESCE 深度對比

核心差異速覽表

1. 資料類型優先級差異

DECLARE @int INT, @datetime DATETIME
-- COALESCE 依優先級：DATETIME > INT
SELECT COALESCE(@datetime, 0)      -- 回傳 DATETIME
SELECT COALESCE(@int, CURRENT_TIMESTAMP) -- 回傳 DATETIME

-- ISNULL 依第一參數類型
SELECT ISNULL(@datetime, 0)        -- 回傳 DATETIME
SELECT ISNULL(@int, CURRENT_TIMESTAMP)   -- 錯誤：無法將 DATETIME 轉 INT

ISNULL 需顯式轉換：ISNULL(@int, CONVERT(INT, CURRENT_TIMESTAMP))

2. 字串截斷風險

DECLARE @c5 VARCHAR(5)
SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang')  -- 完整回傳 12 字元
UNION ALL
SELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang')      -- 截斷為 5 字元

驗證欄位屬性差異：

DECLARE @c5 VARCHAR(5)
SELECT c = COALESCE(@c5, 'Jeffcky Wang'), i = ISNULL(@c5, 'Jeffcky Wang')
INTO dbo.TestISNULL_COALESCE
SELECT name, TYPE_NAME(system_type_id) AS t, max_length, is_nullable 
FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.TestISNULL_COALESCE')

COALESCE 結果可為 NULL，ISNULL 結果強制非 NULL。

3. 計算欄與 PERSISTED 要求

-- ISNULL 可直接建主鍵
CREATE TABLE dbo.CreateISNULL (a INT, b AS ISNULL(a, 15) PRIMARY KEY) -- 成功

-- COALESCE 需 PERSISTED
CREATE TABLE dbo.CreateCOALESCE (a INT, b AS COALESCE(a, 15) PERSISTED PRIMARY KEY) -- 成功

4. 多參數處理便利性

-- COALESCE 簡潔
SELECT COALESCE(a, b, c, d, e, f, g) FROM dbo.table

-- ISNULL 需巢狀嵌套
SELECT ISNULL(a, ISNULL(b, ISNULL(c, ISNULL(d, ISNULL(e, ISNULL(f, g)))))) FROM dbo.table

兩者最終皆轉換為巢狀 CASE 表達式執行。

5. 微效能測試（50萬次迴圈）

四種場景（雙 NULL、首 NULL、尾 NULL、雙非 NULL）測試顯示：效能差異極微，可忽略不計。執行計劃亦基本一致。

6. 特殊陷阱：ISNULL(NEWID(), ...) 的 NULLABILITY 異常

SELECT ISNULL(NEWID(), 'JeffckyWang') AS Col1 INTO dbo.IsNullExample2
EXEC sp_help 'dbo.IsNullExample2'

資料實際非 NULL，但欄位屬性卻標示為可 NULL —— 潛在風險來源。

最佳實踐建議

• 優先使用 COALESCE： 標準相容、多參數支援、語意清晰（合並非替換）。
• ISNULL 適用場景： 簡單雙參數替換、需強制非 NULL 欄位屬性、舊代碼維護。
• 字串拼接： SQL 2012+ 推薦 CONCAT()，自動處理 NULL 且無參數限制。