比如，我要建立一個(gè)1，000，000行的數(shù)字表：
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 = @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc = @max;
這種方式非常巧妙，它并不是一個(gè)一個(gè)的循環(huán)插入，而是一次插入很多行，{1}，{2}，{3，4}，{5，6，7，8}。。。
為什么這樣會(huì)快呢？
是因?yàn)樗?jié)省了跟比較其他可用解決方案進(jìn)行比較和記錄這些日志的時(shí)間。
然后，作者給了一個(gè)CTE的遞歸的解決方案:
DECLARE @n AS BIGINT;
SET @n = 1000000;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n @n
)
SELECT n FROM Nums
OPTION(MAXRECURSION 0);--為了移除默認(rèn)100的遞歸限制
有個(gè)更優(yōu)的CTE的解決方案，就是先生成很多行，然后用ROW_NUMBER進(jìn)行計(jì)算，再選擇ROW_NUMBER這列的值就可以了。
復(fù)制代碼 代碼如下:
DECLARE @n AS BIGINT;
SET @n = 1000000;
WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Base WHERE n CEILING(SQRT(@n))
),
Expand AS
(
SELECT 1 AS c
FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
FROM Expand
)
SELECT n FROM Nums WHERE n = @n
OPTION(MAXRECURSION 0);
利用笛卡爾積進(jìn)行不斷的累加，達(dá)到了22n行。
最后，作者給出了一個(gè)函數(shù)，用于生成這樣的數(shù)字表：
復(fù)制代碼 代碼如下:
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n = @n;
GO
您可能感興趣的文章:- SQLServer用t-sql命令批量刪除數(shù)據(jù)庫(kù)中指定表(游標(biāo)循環(huán)刪除)
- 如何在SQL Server 2008下輕松調(diào)試T-SQL語(yǔ)句和存儲(chǔ)過(guò)程
- SQL Server中的T-SQL的基本對(duì)象
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記（3）
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記（2）
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記（1)
- SQLServer 2008 新增T-SQL 簡(jiǎn)寫語(yǔ)法
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- T-sql語(yǔ)句修改SQL Server數(shù)據(jù)庫(kù)邏輯名、數(shù)據(jù)庫(kù)名、物理名的方法
