ROW_NUMBER() - Automatic Paging


SQL Tips

Add a Unique Constraint to a Column

CURSOR

DELETE

Dirty Reads and Phantom Reads

INSERT

Recursive SQL

ROW_NUMBER() - Automatic Paging

SELECT, FROM

SQL Default Date Format

UPDATE

WHERE

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

ROW_NUMBER() - Automatic Paging

Here we look at how to how to use Row_Number in SQL Server.

When we you C# and connect with SQL Server to retrieve a results set of data, sometimes the data can contain hundreds or more rows of data. This adds a certain performance cost in not only getting SQL Server to retrieve each of those rows from the database, but also in transporting all of that data across the network connection to the C# code which processes the data, and even the C# will be hit with to receive all those rows and process all the data into objects or however your handling that.

Paging

One way to reduce the performance cost is to reduce the number of rows retreieved in SQL, less rows been transported across the network and also less rows being processed in the .NET application. Paging can help to achieve this, such as you have 100 results, but you only see 10 results at a time. Only 10 rows are passed over the network, and 10 rows are processed in .NET. In the past I myself would have coded the SQL to create a temporary table and populate this with the results of my initial query, and adding an extra column which would contain an incrementing integer ID. From that I could then select a subset of the query and return just 10 rows based on the extra column's id.

ROW_NUMBER()

Since SQL Server 2005, ROW_NUMBER() has been provided in SQL Server to provide paging of results, but automatically and without having to produce the extra code. Producing the code could be time consuming and more prone to errors. ROW_NUMBER() acts as an additional column, and we can specify which column (and in what order) to use to base the id column on, even if this is different from the ordering of your main query. For instance so your main query may order alphabetically on a varchar column, with ROW_NUMBER() making the ids incrementing on each row, based on the alphabetical order of that varchar column, or, you can still order your results alphabetically on the varchar, but have the ROW_NUMBER() id column ordered by a different column such as Datetime column.

Example of Row_Number() - Automatic Paging


WITH pagingData AS

(SELECT TOP 1000 [ID]
,ROW_NUMBER() OVER (ORDER BY [TimeStamp] desc) as 'RowNumber'
,[TimeStamp]
,[Server]
,[CommandLog]
FROM [MyDataBase].[dbo].[MyTable])

SELECT * FROM pagingData
WHERE [RowNumber] BETWEEN 21 and 40


So here we select the top 1000 rows from MyTable, and add an extra column using ROW_NUMBER(), which adds numbers from 1 onwards, adding a one for each row, and we have this number becoming added to rows in the order of the column [TimeStamp], in descending order (This is as if the query is first run in this order, with id row incrementing on each row, then the main query can order on whatever it wants, as the ids have already been added). This all goes into 'pagingData' from where we can select a subset of data, in this case rows 21 to 40.