[SQL Server] How to obtain the serial number

-- SQL2000

 

-- 1

Select number from Master... spt_values where type = 'P' -- 0-255

 

-- 2
Select top 10000 id = identity (INT,) into # T from sysobjects, syscolumns

 

-- Sql2005 generate series numbers (row numbers)
-- 1. Recursive test with CTE
; With T
(
Select 1 as num
Union all
Select num + 1
From t
Where num <100000
)
Select * from t
Option (maxrecursion 0)

-- 2. Generate a row number using the system table
Select top 100000 num = row_number () over (order by getdate ())
From syscolumns A, syscolumns B

-- 3. Generate a digital table with High Efficiency
Create Function DBO. fn_nums (@ n as bigint)
Returns table
As
Return
With
T1 as (select 1 as C Union all select 1 ),
T2 as (select 1 as C from T1 as A, T1 as B ),
T3 as (select 1 as C from T2 as A, T2 as B ),
T4 as (select 1 as C from T3 as A, T3 as B ),
T5 as (select 1 as C from T4 as A, T4 as B ),
T6 as (select 1 as C from T5 as A, T5 as B ),
T7 as (select row_number () over (order by C) as N from T6)
Select n From T7 where n <@ n;
Go
-- Test

Select * From DBO. fn_nums (1000)