SQL Server implements digital secondary table instance code _mssql

A digital auxiliary table is a sequential sequence of integers, usually used to implement many different query tasks. Most are divided into two categories: large physical numbers and table functions, the former can be called Static, the latter can be called dynamic and on-demand production.

Physical Number Table

Physical numeric tables usually have a physical table, the table records are relatively large enough, and the relevant T-SQL code is as follows:

IF object_id (N ' dbo. Nums ', ' U ') is not NULL
BEGIN
  DROP TABLE dbo. Nums;
End
Go
 
CREATE TABLE dbo. Nums 
(
  num INT not NULL,
  CONSTRAINT pk_u_cl_nums_num PRIMARY KEY CLUSTERED
  (
    num ASC
  ) 
);
Go
 
inserts into dbo. Nums (Num)
SELECT row_number () Over (order by (SELECT NULL)) as rownum from
master.dbo.spt_values;
Go

Note: There are many ways to populate a physical digital table, using one for demonstration purposes.

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

1 SELECT Num
2 from dbo. Nums;
3 Go

The results of the executed query are as follows:

Table Functions

The table function implementation uses the cross joins and Cte,sql Server 2005 and above versions of T-SQL code as follows:

IF object_id (n ' dbo.ufn_getnums ', n ' IF ') is not NULL BEGIN DROP TABLE dbo.ufn_getnums;
End Go--==================================-function: Get the specified range of numeric sequences--Description: The data rows obtained by a CTE at the cross last level: The total number of rows from the L-level (counting from 0) is 2^2^l. For example: at level 5, you get 4 294 967 596 rows.
The 5-level CTE provides more than 4 billion rows. --Author: xxx--Create: YYYY-MM-DD--Modify: Yyyy-mm-dd XXX Modify Content Description--================================== Create FUNCTION dbo.ufn_get Nums (@bintLow BIGINT, @bintHigh BIGINT) RETURNS TABLE as return with L0 as (SELECT C from (VALUES (1), (1)) A S LO (c)), L1 as (select 1 as C-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 to 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 () NULL) as rownum from L5, SELECT top (@bintHigh-@bintLow + 1) @bintLow + RowNum-1 as Num from Nums order by Ro
Wnum ASC; Go

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

IF object_id (n ' dbo.ufn_getnums2 ', n ' IF ') is not NULL BEGIN DROP TABLE dbo.ufn_getnums2;
End Go--==================================-function: Get the specified range of numeric sequences--Description: The data rows obtained by a CTE at the cross last level: The total number of rows from the L-level (counting from 0) is 2^2^l. For example: at level 5, you get 4 294 967 596 rows. 
The 5-level CTE provides more than 4 billion rows. --Author: xxx--Create: YYYY-MM-DD--Modify: Yyyy-mm-dd XXX Modify Content Description--================================== Create FUNCTION dbo.ufn_get NUMS2 (@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-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 to L2 as T CROSS join L2 as T2), L4 as (select 1 as C from L3 as T CROSS join L3 A  S T2), L5 as (select 1 as C from L4 as T CROSS JOIN L4 as T2), nums as (select Row_number ()  NULL) as rownum from L5 SELECT @bintLow + RowNum-1 as Num to Nums order by rownum ASC OFFSET 0 ROWS FETCH @binthigh-@bintLow + 1 ROWS only; Go

Take the function Ufn_getnums as an example to demonstrate the related effect. Gets the T-SQL code for the specified range of numeric sequences as follows:

SELECT Num
from Dbo.ufn_getnums (one);
Go

The results of the executed query are as follows:

Bo Friends If there are other better solutions, please do not hesitate to enlighten, extremely grateful.

List of reference lists

1. Author of Microsoft SQL Server high-performance T-SQL Using Window functions Itzik Ben-gan (United States) (SQL Server Inside book author ）

Thank you for reading, I hope to help you, thank you for your support for this site!