[sql server] 如何得到連續序號

--SQL2000

 

--1

select number from master..spt_values where type='p' --0-255

 

--2
select top 10000 id=identity(int,1,1) into #t from sysobjects,syscolumns 

 

--SQL2005產生系列號（行號）兩種方式 
--1.用CTE遞迴測試  
;WITH t AS   
(   
    SELECT 1 AS num  
    UNION ALL  
    SELECT num+1   
    FROM t  
    WHERE num<100000  
)  
SELECT * FROM t   
OPTION(MAXRECURSION 0)
 
--2.用通過系統資料表產生行號測試  
SELECT TOP 100000 num=ROW_NUMBER()OVER(ORDER BY GETDATE())  
FROM syscolumns a,syscolumns b  

--3.產生一個數字表，效率非常高
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
--測試

select * from dbo.fn_nums(1000)