Being a web developer, you may often need to generate sequential numbers for listing pages. When using SQL server 2000, you may find it quite hard to implement this. Fortunately, Microsoft has introduced a handy ranking function Row_Number () in TSQL 2005. It cuts down a significant amount of effort needed for sequential number generation using SQL queries.
For example, we have a database table as described by the following SQL script
CREATE TABLE [dbo].[Details](
[Name] varchar(50),
[Age] int,
[Gender] varchar(10)
)
Note that the Details table has no column for Serial Numbers. So, we have to generate them on the fly while rendering the listing page to display the contents of the Details table. Below is the simple SQL query which uses Row_Number() function to generate the sequential numbers.
SELECT ROW_NUMBER() OVER(ORDER BY [Name]) As Row#, [Name], Age, Gender
FROM Details
The Row_Number () function must be immediately followed by Over () function which accepts a required parameter of clause ORDER BY. SQL Server uses the Over function’s input parameter to sort out the data, and then generates consistent row numbers in the result set. In this example the records in the result set are sorted by the Name column and then the row numbers are generated.
Below is a part of the result set generated from our database when the above query is executed.
The Row_Number() function can be useful in listing pagination. Normally .Net controls like gridview and Datagrid provide built-in pagination functionality. When we use these controls the entire result set needs to be transferred from the database server to the web server. This results in significant performance overhead when the result set contains thousands of records. So we can avoid transferring entire result set to the web server by handling pagination at the database server end.
Now let us modify the above query so as to select only the required records for a specific page index. If we assume that the number of records per page is 10 and we are about to display the 11th page then the query will be:
Select SNo,[Name], Age, Gender from (
SELECT ROW_NUMBER() OVER(ORDER BY [Name]) As SNo, [Name], Age, Gender
FROM Details) as Test WHERE SNo > 100 AND SNo <= 110
This will return only the 10 records required for the 11th page of your listing and we do not need to use the pagination functionality of gridview like controls. Thus It improves the performance and efficiency of the web application. To go further, you can try writing a stored procedure accepting the page size and page index as input parameters and using it in your application.