How to page large data sets in MSSQL

How To
1

When working with large data sets, and sending the data over a REST Host to show on a screen, sometimes you don’t want to send a huge amount of data at once. For instance, if you’ve got 10,000 records in your database and only want to show 50 at a time on the screen, it doesn’t make sense to send 10,000 over your API every time a user views the screen. In these cases it would be a good idea to do paging before sending the data over your REST API. Even better, do the paging on the SQL server before Linx receives it.

Here’s an easy way to page data sets on MS SQL:

First, as in input into the REST call I’d had 2 parameters:

  • PageSize: This would be how many records to pull at a time
  • PageNumber: This is the page on the screen you want to see.

However, to do the SQL, we can’t give it a page number, but need:

  • Offset: This is how deep into the records to start pulling the data. This can be calculated in the following way:

Offset = PageSize * (PageNumber - 1)

Then, you can simply do the SQL call in this way:

SELECT [ID]
      ,[Field1]
      ,[Field2]
      ,[Field3]
  ......
  FROM [LongTable]
  ORDER by ID ASC
  
  OFFSET CONVERT(int,@{Offset}) ROWS 
FETCH NEXT CONVERT(int,@{PageSize}) ROWS ONLY
2 Likes