[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)