How to dynamically number rows in SQL Server

The question of how to dynamically number rows in SQL, plus line numbers, is a classic problem in database queries. I'll sort out the present method and share the skills. The code is based on the pubs boilerplate database. In SQL, these two methods are generally:

1. Use of temporary tables

You can use SELECT into to create a temporary table, in the first column, add identify (int,1,1) as the line number, so that in the resulting temporary table, the result set has the line number. It is also the most efficient method at present. This method cannot be used for views

Code:

set nocount on
select IDentify(int，1，1) 'RowOrder'，au_lname，au_fname into #tmp
from authors
select * frm #tmp
drop table #tmp

2. Using a self-connection

Do not use temporary tables, in SQL statements, dynamic sorting. The connection used for this method is a self connection, which is generally greater than the connection.

Code:

<

Code>select Rank=count (*), A1.au_lname, A1.au_fname
From authors A1 inner
Join authors A2 on A1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
Group BY A1.au_lname, A1.au_fname
Order BY Count (*)

Run Result:

TD style= "BORDER:0;PADDING:0;" >defrance " TD style= "BORDER:0;PADDING:0;" >ann

rank	au_lname	au_fname
1	bennet	abraham
2	blotchet-halls	reginald
carson< /td>	cheryl
4	michel
5< /td>	del Castillo	innes
dull
greene	morningstar

Disadvantages:

1. Use a self-join, so this method does not work with a large number of rows. It works with hundreds of rows. For large tables, be sure to use an index to avoid a wide range of searches, or use the first method.

2. Cannot handle duplicate values normally. When you compare duplicate values, discontinuous row numbers appear. If you do not want this behavior, you can hide the row sequence when you insert the result in the spreadsheet, using the spreadsheet number, or the first method

Advantages:

1. These queries can be used in view and result formatting

Inserting the line number in the result set, you can now cache the result collection and then use DataView to add the filter condition

RowNum>PageIndex*PageSize And RowNum<=(PageIndex+1)*PageSize

You can achieve fast paging, and no matter what your page data-bound controls are (Datalist,datagrid, or repeate). If you are using a DataGrid, it is not recommended to use this technique. Because the DataGrid's paging efficiency is almost the same as it is.