SQL2005 added 4 functions on Team computing: the Row_number,rank,dense_rank,ntile, respectively.
Note: These functions can only appear in queries for select and order by. The syntax is as follows:
Ranking_function over ([partition by col_list] ORDER by col_list)
Row_number: A sequential number of all columns is identified on a sort basis.
Order of execution: to compute the column values, the optimizer first needs to sort the data on the partitioned columns, and then encodes the columns.
Technical processing of column calculations before SQL2005
1. (set-based)
Before SQL2005, there was a simple calculation of the set of columns, using the unique partitioning + sort combination.
For example, you can use the following techniques:
SELECT empid, (COUNT fromdboWHERE S2<= S1. Empid from dbo order byEmpid;
This is very simple, but also very slow ...
You can do this if you need to combine conditions to produce the number of columns (that is, a combined sort and circuit breaker for a Non unique column):
SELECT empid, qty, (COUNT fromdboWHERE S2< S1OR S2= S1and S2 <= C20>.empidfrom dboORDER by qty, empid;
Of course, there are many ways, such as using cursors, do not write examples.
2. (identity-based Solution)
SELECT empid , qty , IDENTITY ( int , 1 , 1 ) as RN into #SalesRN from dbo . Sales ; SELECT * from #SalesRN ; DROP TABLE #SalesRN ;