The number is automatically added to the SQL Server query result set.

SQL provides an identity function to obtain the value of the ID column. Unfortunately, this function can only be used in select into statements, so we have to introduce a temporary table.
Ex:
Use pubs
Select Identity (INT, 1, 1) As rank, au_lname, au_fname
Into # TMP
From authors
Select * from # TMP
Drop table # TMP
This method has good performance, but the disadvantage is that it can only be completed through several SQL statements.
If possible, we recommend that you complete this operation on the client.

 

Sqlserver2005 and later versions can be implemented using analysis functions.

 

 

 

Compared with SQL Server 2005, SQL Server 2000 is greatly improved, and some of them are very practical.

Here are a few examples to illustrate these examples. I have referenced the northwind library.

1. Top expression
The top of SQL Server 2000 is a fixed value. Is it uncomfortable? It is improved now.

-- Orders with the first n names
Declare @ n int
Set @ n = 10
Select top (@ n) * from orders

2. Paging
I don't know how you paging SQL Server 2000 in the past, and most of them use temporary tables. SQL Server 2005 supports paging in one sentence, and the performance is also said to be very good.

-- Sort by freight from small to large and obtain the result of 20 to 30 rows
Select * from (
Select orderid, freight, row_number () over (order by freight) as row from orders
)
Where row between 20 and 30

3. Ranking

Select * from (
Select orderid, freight, rank () over (order by freight) as rank from orders
)
Where rank between 20 and 30

4. Try... catch
SQL Server 2000 has no exception and the T-SQL must check errors row by rowCodeTry catchProgramClerk, 2005 is more cordial:

Set xact_abort on -- enable the try Function
Begin try
Begin tran
Insert into orders (customerid) values (-1)
Commit tran
Print 'commited'
End try
Begin catch
Rollback
Print 'rolled back'
End catch

5. General Expression CTE
You can use expressions to create temporary tables in the past.
Www.knowsky.com

-- Example: Paging with a common expression
With orderfreight (
Select orderid, freight, row_number () over (order by freight) as row from orders
)
Select orderid, freight from orderfreight where row between 10 and 20
In particular, expressions also support recursion.