select row_number() over(order by [date] asc) id,[date] from [你的表]

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

Declare @A Table (ID Int, dDate Date)
Insert Into @A
Select 1, '2005-5-21'
Union All Select 2,'2005-5-20'
Union All Select 4,'2005-5-22'
Union All Select 9,'2005-5-25'
Union All Select 10,'2005-5-27'
Union All Select 13,'2005-4-1'
Union All Select 17,'2005-5-26'
Union All Select 18,'2005-5-30' 

Select ROW_NUMBER() OVER (Order By dDate Asc) ID, dDate From @A
/*
ID                   dDate
-------------------- ----------
1                    2005-04-01
2                    2005-05-20
3                    2005-05-21
4                    2005-05-22
5                    2005-05-25
6                    2005-05-26
7                    2005-05-27
8                    2005-05-30
*/

Update A Set A.ID=B.ID
From @A A,(Select ROW_NUMBER() OVER (Order By dDate Asc) ID, dDate From @A) B Where A.dDate=B.dDate

Select * From @A
/*
ID          dDate
----------- ----------
3           2005-05-21
2           2005-05-20
4           2005-05-22
5           2005-05-25
7           2005-05-27
1           2005-04-01
6           2005-05-26
8           2005-05-30
*/

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

select 
    row_number() over (order by date) as id,
    date 
from tablename