Apply row_number () in SQL Server

Method 1:

With temp

(Select row_number () over (order by cityid) as rownum, cityname from City)

Select * from temp where rownum between 10 and 20

Go

Explanation:

1. This method places the query results in parentheses in the variable temp (I am not sure if it is a variable), and uses the row_number () function for row number tracking, use the over function to sort a column (required) and specify the column name as 'rownum'

2. In the following statement, you can query 'rownum' with a specified row number.

3. After this batch of statements is executed, the variable temp is released.

Method 2:

Select Identity (INT, 1, 1) as rownum, cityname into # temp from City

Select * from # temp where rownum between 10 and 20

Go

Explanation:

This method is similar to the above, except that the row_number () function is replaced with the identity () function.

Put the result set in a temporary table. When the batch statement execution is complete, this temporary table can also be used

How does SQL Server restore the sequence number of an automatically numbered column?
This problem often occurs in SQL Server:
after a table uses an automatically numbered column, as many data have been tested, tens of thousands of automatic numbers have been accumulated. Now we are using this table, and the test data has been deleted. The legacy problem is that we are entering new data and the number will only be increased, deleted numbers cannot be used. Who knows how to solve this problem?
the truncate command not only clears all data, but also restores the seed value of identity to the original value.
DBCC checkident is more convenient. You can specify the seed value without deleting data.
1. truncate table tablename
2. DBCC checkident (tablename, reseed, 1)