ROW_NUMBER() - Automatic Paging


InterServer Web Hosting and VPS
Sony Playstation PS4 Pro Custom Build NVMe SSD Drive - Enclosure case and Crucial NVMe SSD
PS4 Pro Custom External SSD NVMe Drive

How I built a PS4 Pro Custom External SSD NVMe Drive...

NUC8i7BEH with memory
Intel NUC8I7BEH Review and Custom Build

This is my review of the Intel NUC8i7BEH and a summary of my build...

Card image cap
Asus M5A88-M EVO Review

This is my review of the Asus M5A88-M EVO motherboard...

Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit
Corsair Vengeance Blue Review

This is my review of the Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit memory...

Robotic Arm with USB PC Interface Review
Robotic Arm with USB PC Interface Review

This is my review of the Robotic Arm with USB PC Interface...

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.

Sponsored Links
SolarSystem - AngularJS Demo
SolarSystem - Angular JS Demo

Check out my first Angular JS demo hosted in Azure, it helped win my first Angular contract. Put the sound on.