數字輔助表 是一組連續整數序列，廣泛用於分頁、日期序列產生、字串拆解、缺口分析等查詢任務。實作分兩大類：靜態實體表（預先產生足夠大的資料集）與動態表值函數（按需即時產生）。本文提供兩種完整 T-SQL 實作碼，並說明效能考量。
建立實體表並填充資料，適合頻繁、大量存取的情境。
IF OBJECT_ID(N'dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums (
Num INT NOT NULL,
CONSTRAINT PK_U_CL_Nums_Num PRIMARY KEY CLUSTERED (Num ASC)
);
GO
-- 利用系統表 spt_values 快速產生序列
INSERT INTO dbo.Nums (Num)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM master.dbo.spt_values;
GO
SELECT Num FROM dbo.Nums;
使用多層 CTE 交叉聯結（Cross Join）指數級產生序列，再配合 ROW_NUMBER 產生連續數字。L0 2 列，L1 4 列，L2 16 列，L3 256 列，L4 65,536 列，L5 超過 40 億列，足夠應付絕大多數需求。
IF OBJECT_ID(N'dbo.ufn_GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.ufn_GetNums;
GO
CREATE FUNCTION dbo.ufn_GetNums (
@bintLow BIGINT,
@bintHigh BIGINT
)
RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS LO(c)),
L1 AS (SELECT 1 AS c FROM L0 AS T CROSS JOIN L0 AS T2),
L2 AS (SELECT 1 AS c FROM L1 AS T CROSS JOIN L1 AS T2),
L3 AS (SELECT 1 AS c FROM L2 AS T CROSS JOIN L2 AS T2),
L4 AS (SELECT 1 AS c FROM L3 AS T CROSS JOIN L3 AS T2),
L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)
SELECT TOP (@bintHigh - @bintLow + 1)
@bintLow + RowNum - 1 AS Num
FROM Nums
ORDER BY RowNum ASC;
GO
CREATE FUNCTION dbo.ufn_GetNums2 (
@bintLow BIGINT,
@bintHigh BIGINT
)
RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS LO(c)),
L1 AS (SELECT 1 AS c FROM L0 AS T CROSS JOIN L0 AS T2),
L2 AS (SELECT 1 AS c FROM L1 AS T CROSS JOIN L1 AS T2),
L3 AS (SELECT 1 AS c FROM L2 AS T CROSS JOIN L2 AS T2),
L4 AS (SELECT 1 AS c FROM L3 AS T CROSS JOIN L3 AS T2),
L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)
SELECT @bintLow + RowNum - 1 AS Num
FROM Nums
ORDER BY RowNum ASC
OFFSET 0 ROWS FETCH FIRST @bintHigh - @bintLow + 1 ROWS ONLY;
GO
-- 取得 11 到 20 的數字序列 SELECT Num FROM dbo.ufn_GetNums(11, 20);
| 維度 | 靜態實體表 | 動態表值函數 |
|---|---|---|
| 首次建立成本 | 較高（需填充資料） | 極低（僅建立函數） |
| 查詢效能 | 最佳（有索引、統計資訊） | 極佳（內存運算、無 I/O） |
| 儲存空間 | 佔用磁碟 | 不佔用 |
| 維護 | 需定期檢查範圍是否足夠 | 自動依參數產生，無上限 |
| 適用場景 | 高頻、大範圍、需 Join 索引 | 臨時、參數化範圍、開發測試 |
參考書籍：《Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions》— Itzik Ben-Gan
