SQL Server implements the code of a digital auxiliary table instance.

SQL Server implements the code of a digital auxiliary table instance.

The Number Auxiliary table is a continuous integer series, which is usually used to implement multiple different query tasks. Most of them are divided into two categories: a large enough physical number table and a table function. The former can be called static, and the latter can be called dynamic and on-demand production.

Physical number table

The physical number table usually has a physical table, the table record is relatively large enough, the related T-SQL code is as follows:

IF OBJECT_ID(N'dbo.Nums', 'U') IS NOT NULLBEGIN  DROP TABLE dbo.Nums;ENDGO CREATE TABLE dbo.Nums (  Num INT NOT NULL,  CONSTRAINT PK_U_CL_Nums_Num PRIMARY KEY CLUSTERED  (    Num ASC  ) );GO INSERT INTO dbo.Nums (Num)SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumFROM master.dbo.spt_values;GO

Note: There are many ways to fill the physical number table. One method is used for demonstration.

The T-SQL code for the test is as follows:

1 SELECT Num2 FROM dbo.Nums;3 GO

The query result after execution is as follows:

 Table Functions

The T-SQL code for table function implementation using crosstab and CTE, SQL Server 2005 and later versions is as follows:

IF OBJECT_ID (n' dbo. ufn_GetNums ', n'if') is not nullbegin drop table dbo. ufn_GetNums; ENDGO -- ======================================================== -- function: obtain the number series in the specified range -- Note: the number of rows obtained by the CTE at the last level of the Cross: the total number of rows obtained at the L level (counted from 0) is 2 ^ 2 ^ L. -- For example, you can get 4 294 967 rows at level 5. The CTE of Level 5 provides more than 4 billion rows. -- Author: XXX -- create: yyyy-MM-dd -- modify: description of yyyy-MM-dd XXX modification -- ============================== ==== create function dbo. ufn_GetNums (@ bintLow BIGINT, @ bintHigh BIGINT) returns tableasreturn 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

SQL Server 2012 adds new paging-related features and the related T-SQL code is as follows:

IF OBJECT_ID (n' dbo. ufn_GetNums2 ', n'if') is not nullbegin drop table dbo. ufn_GetNums2; ENDGO -- ======================================================== -- function: obtain the number series in the specified range -- Note: the number of rows obtained by the CTE at the last level of the Cross: the total number of rows obtained at the L level (counted from 0) is 2 ^ 2 ^ L. -- For example, you can get 4 294 967 rows at level 5. The CTE of Level 5 provides more than 4 billion rows. -- Author: XXX -- create: yyyy-MM-dd -- modify: description of yyyy-MM-dd XXX modification -- ============================== ==== create function dbo. ufn_GetNums2 (@ bintLow BIGINT, @ bintHigh BIGINT) returns tableasreturn 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

The following uses the ufn_GetNums function as an example to demonstrate the effect. The T-SQL code for getting a sequence of numbers within a specified range is as follows:

SELECT NumFROM dbo.ufn_GetNums(11, 20);GO

The query result after execution is as follows:

 

If you have other better solutions, thank you very much.

Reference List

1. Author of Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions Itzik Ben-Gan (USA) (author of SQL Server Inside related books)

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!