--> 测试数据：[user]
if object_id('[user]') is not null drop table [user]
create table [user]([pkid] int,[name] varchar(6),[birthday] int,[snumber] int)
insert [user]
select 1,'bary',1987,1001 union all
select 3,'annie',1984,1002 union all
select 6,'peter',1984,1003 union all
select 7,'willim',1985,1004

select *,
(select COUNT(pkid) from [user] b where b.pkid<a.pkid) as sortnumber
 from [user] a
 /*
 pkid    name    birthday    snumber    sortnumber
1    bary    1987    1001    0
3    annie    1984    1002    1
6    peter    1984    1003    2
7    willim    1985    1004    3
 */

------解决方案--------------------
直接*,ROW_NUMBER()OVER(ORDER BY GEDATE()) AS SORTNUMBER FROM TB就可以了。

------解决方案--------------------
SQL code

select pkid,name,birthday,snumber,
row_number over(order by getdate()) 'sortnumber'
from user