Sometimes we need to implement paging functionality in our database. We can use ROW_NUMBER() function for this purpose. ROW_NUMBER function is one of the ranking function of SQL Server. Here is an example of ROW_NUMBER -
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY EmpID) AS Row,
* FROM Employee AS tbl ) WHERE Row >= 1 AND Row <= 10
ROW_NUMBER is used for assigning sequential numbers to the records of record-set. In the above query a sequel number is assigned to each record of the Employee table.
Here we have used following two clause -
- OVER clause - It is used for specifying the ordering of records before assigning the sequence to each record.
- ORDER clause - In order clause we define column or columns which is used to order the records in the record-sets. In our case we have used EmpId with ORDER clause, so first each record of Employee table is first ordered according to EmpId and then sequence number is assigned to each record.
*PARTITION clause can also be used along with OVER and ORDER clause (optional). Partition clause allows user to create some partition or sub record-sets as per the given column.
Once a sequence number is assigned to each record of the Employee table, then we can use each Row of record-set as one page.
eProject
