Question[SQL]: How can I add row numbers to my result set?

Question: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:
line-no  title_id
----------- --------
1  BU1032
2  BU1111
3  BU2075
4  BU7832
5  MC2222
6  MC3021
7  MC3026
8  PC1035
9  PC8888
10 PC9999
11 PS1372
12 PS2091
13 PS2106
14 PS3333
15 PS7777
16 TC3218
17 TC4203

18 TC7777

 

Answer：

SQL2005版本：

SELECT ROW_NUMBER() over(order by [title_id]) as [line-no], [title_id]
  FROM [pubs].[dbo].[titles]
  order by [title_id]

 

SQL2000版本：

declare @dt table(line_no int identity(1,1), title_id varchar(6))
insert into @dt(title_id)
    select title_id from [pubs].[dbo].[titles] order by [title_id]
select * from @dt;