{"id":96331,"date":"2023-03-20T21:37:35","date_gmt":"2023-03-20T21:37:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96331"},"modified":"2023-06-06T19:20:24","modified_gmt":"2023-06-06T19:20:24","slug":"paging-data-in-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/paging-data-in-t-sql\/","title":{"rendered":"Paging Data in T-SQL"},"content":{"rendered":"

This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, click here<\/a>.<\/strong><\/p>\n<\/p>\n

Sometimes when working with very large sets of data, you may not want to return all of the data at once. I discussed using TOP<\/a> in my previous article, which allowed you to only get a number of rows from the start of the results from a query. However, if you want to see the rows after that top set,<\/p>\n

paging of data takes that further to let you scroll through a set of data one page at time. So, you might want to fetch the first 100 rows, then another 100, then the rest of the rows, etc.<\/p>\n

This article will show you how to page through a set of results using the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause.<\/p>\n

Sample Data<\/h2>\n

Before showing some different paging options, I will create some test data using the code in Listing 1.<\/p>\n

-- Create test data\r\nUSE tempdb;\r\nGO\r\n\r\nDROP TABLE IF EXISTS TestData; \r\nGO\r\n\r\nCREATE TABLE TestData (\r\nID INT IDENTITY, \r\nCityName VARCHAR(20),\r\nStateName VARCHAR(20),\r\nFounded SMALLINT);\r\n\r\n--Insert rows of test data\r\nINSERT INTO TestData VALUES\r\n('Seattle','Washington',1851),\r\n('Redmond','Washington',1871),\r\n('Bellevue','Washington',1953),\r\n('Spokane','Washington',1881),\r\n('Tacoma','Washington',1872),\r\n('Portland','Oregon',1851),\r\n('Grants Pass','Oregon',1887),\r\n('Salem','Oregon',1842),\r\n('Bend','Oregon',1905);<\/pre>\n
Listing 1: Creating Sample Data<\/strong><\/p>\n
In Listing 1, a table named TestData<\/code> was created that contains a list of cities. This data will be used in the different paging examples below. If you want to follow along and run the example code in this article you can create the sample TestData<\/code> on your test instance of SQL Server.<\/p>\n
Page through data in T-SQL<\/h1>\n
In order to page through a page of data using T-SQL the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause are used. SQL Server starts returning data from a specific row based on the OFFSET<\/code> value and returns a specific number of rows based on the FETCH<\/code> value.<\/p>\n
Using the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause is a better option for paging then using a server-side cursor<\/a>.<\/p>\n
Syntax for the OFFSET and FETCH<\/h3>\n
Below is the syntax for the ORDER BY<\/code> clause as found in the Microsoft Documentation<\/a>.<\/p>\n
ORDER BY order_by_expression \r\n  [ COLLATE collation_name ]  \r\n  [ ASC | DESC ]  \r\n  [ ,...n ]  \r\n[ <offset_fetch> ] \r\n<offset_fetch> ::= \r\n{  \r\n  OFFSET { integer_constant | offset_row_count_expression } \r\n      { ROW | ROWS } \r\n  [ \r\n   FETCH { FIRST | NEXT } {integer_constant | \r\n      fetch_row_count_expression } { ROW | ROWS } ONLY \r\n  ] \r\n}} <\/pre>\n
The <offset_fetch> <\/em>option is an optional item that is used in conjunctions with the ORDER BY<\/code> clause to page through a set of data. It has two components:<\/p>\n
OFFSET<\/code> and FETCH<\/code>. <\/em><\/p>\n
The OFFSET<\/code> option identifies the number of rows in an ordered row set to skip before rows are returned. The FETCH<\/code> option is optional and identifies the number of rows that will be returned. If the FETCH<\/code> option is not specified all rows from the OFFSET<\/code> location to the end of the ordered set are returned.<\/p>\n
To show how the OFFSET<\/code> and FETCH<\/code> clauses let\u2019s go through a few different examples.<\/p>\n
Using the OFFSET option<\/h3>\n
The OFFSET<\/code> option of the ORDER BY<\/code> clause is used to identify the number of rows to skip in a record set before rows are returned. The value can be from 0 (zero) or any number up to the number of rows in the set. When zero (0) is used no rows are skipped, as shown when the code in Listing 2 is executed.<\/p>\n
SELECT * \r\nFROM TestData\r\nORDER BY ID \r\nOFFSET 0 ROWS;<\/pre>\n
Listing 2: Skipping zero rows.<\/strong><\/p>\n
When Listing 2 is executed the output showing in Report 1 is created.<\/p>\n
\"A<\/p>\n
Report 1: Output when Listing 2 is run.<\/strong><\/p>\n
As you can see when Listing 2 is run every row in table TestData<\/code> is returned. In Listing 2 no rows were skipped because 0 (zero) was used for the OFFSET<\/code> value and the FETCH<\/code> option is not provided so all rows are returned from the sample data table.<\/p>\n
Suppose the first 5 rows based on ID values needed to be skipped when selecting data. To meet that requirement the code in Listing 3 could be executed.<\/p>\n
-- Skipping 5 rows\r\nDECLARE @Skip INT = 5;\r\nSELECT * FROM TestData\r\nORDER BY ID\r\n OFFSET @Skip ROWS;<\/pre>\n
Listing 3: Skipping 5 rows<\/strong><\/p>\n
The code in Listing 3, this time, specified that 5 rows would be skip. by using a variable instead of a constant. When Listing 3 is executed Report 2 is produced.<\/p>\n
<\/p>\n
Report 2: Output created when Listing 3 is run.<\/strong><\/p>\n
By reviewing the output, in Report 2, you can see only the records with the ID<\/code> value of greater than 5 and a StateName<\/code> value of \u201cOregon\u201d are displayed this time. That is because the first 5 rows in the TestData<\/code> table based on the ID<\/code> value were skipped before the rest of the test data table rows are returned using the SELECT<\/code> statement.<\/p>\n
Each example so far has only shown how to skip rows. If you want to limit the number of rows displayed the FETCH<\/code> option needs to be used.<\/p>\n
Using the FETCH option<\/h3>\n
Assume you what to skip no rows in the record set, but only display just the first three rows of data based on the ID<\/code> column value. If this was the requirement, then the code in Listing 4 could be executed.<\/p>\n
-- Display first 3 rows\r\nDECLARE @Skip INT = 0;\r\nDECLARE @Fetch INT = 3;\r\n\r\nSELECT * \r\nFROM TestData\r\nORDER BY ID\r\n OFFSET @Skip ROWS\r\n      FETCH NEXT @Fetch ROWS ONLY;<\/pre>\n
Listing 4: Displaying the first 3 rows<\/strong><\/p>\n
In Listing 4 another variable was declared @<\/em>Fetch<\/code>, which identifies the number of rows to return. It was set to the value 3. When the code in listing 4 is executed the output in Report 3 was produced.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 3: Output created with Listing 4 is run.<\/strong><\/p>\n
By reviewing Report 3 you can see that zero rows were skipped, as identified by the OFFSET<\/code> value. Plus, only the first 3 rows of the TestData<\/code> table based on the ID<\/code> column were displayed, because the FETCH<\/code> option variable @Fetch<\/code> was set to 3.<\/p>\n
Suppose you wanted to display the first three cities in Oregon based on the ID column value. To accomplish this requirement the code in Listing 5 could be run.<\/p>\n
-- Display first 3 Oregon Cities\r\nDECLARE @Skip INT;\r\nDECLARE @Fetch INT = 3;\r\n\r\nSELECT TOP (1) @SKIP = ID - 1 FROM TestData \r\nWHERE StateName = 'Oregon';\r\n\r\nSELECT * FROM TestData\r\nORDER BY ID\r\n OFFSET @Skip ROWS\r\n      FETCH NEXT @Fetch ROWS ONLY;<\/pre>\n
Listing 5: Displaying first three cities in Oregon. <\/strong><\/p>\n
When the code in Listing 5 is executed the results in Report 4 is displayed<\/p>\n
<\/p>\n
Report 4: Results displayed with Listing 5 is run.<\/strong><\/p>\n
In Listing 5 the @<\/em>Skip<\/code> variable was set programmatically using a SELECT<\/code> statement. That statement identified the ID<\/code> value for the first row that had \u201cOregon<\/code>\u201d set as the StateName<\/code>. <\/em>By programmatically setting the @Skip<\/em> variable, all of the Washington state cities were skipped. Only the first 3 Oregon state rows were displayed because the @Fetch<\/code> variable was set to 3.<\/p>\n
Paging through data with a loop<\/h1>\n
The examples in the prior sections showed how to use the OFFSET<\/code> and FETCH<\/code> options to identify the rows to skip and display from the sample data table. By changing the OFFSET<\/code> and FETCH<\/code> values between calls to SQL Server an application can page through a table of data. This is particularly useful when you need to display one page at a time while paging through a table with a large number of rows. By using the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause will minimize the amount of data transmitted back to the client, by only sending one page data at a time to the application.<\/p>\n
To simulate paging through the sample data my example will use a WHILE<\/code> loop. The code in Listing 6 pages through the sample data displaying 3 rows of data at a time. Keep in mind while you review this example a client application would normally perform the operations of looping through data one page at a time.<\/p>\n
-- Paging through sample data\r\nDECLARE @Skip INT = 0 ;\r\nDECLARE @Fetch INT = 3;\r\nDECLARE @LoopCnt INT;\r\n\r\nSELECT @LoopCnt = COUNT(*) \/ @Fetch \r\nFROM TestData;\r\n\r\nWHILE @LoopCnt > 0\r\nBEGIN\r\n  SET @LoopCnt = @LoopCnt - 1;\r\n\r\n  SELECT * FROM TestData\r\n  ORDER BY ID\r\n  OFFSET @Skip ROWS\r\n  FETCH NEXT @Fetch ROWS ONLY;\r\n\r\n-- Adjust the rows to skip\r\n  SET @SKIP = @SKIP + @Fetch;\r\nEND<\/pre>\n
Listing 6: Paging through sample data 3 rows at a time<\/strong><\/p>\n
The first time through the WHILE<\/code> loop the rows in Report 5 are displayed. This is because the @Offset<\/code> value is set to zero and the @Fetch<\/code> option is set to 3.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 5: First time through the loop.<\/strong><\/p>\n
Before the second time through the loop the @Skip <\/em>value is increased by the value of 3 that is contained in the @Fetch <\/em>variable. The rows in Report 5 are displayed for the second time the loop is executed.<\/p>\n
<\/p>\n
Report 6: Second time through loop. <\/strong><\/p>\n
Before the last time through the loop the @Skip <\/em>is increased again by 3. Report 7 shows the rows displayed for the third time through the loop.<\/p>\n
<\/p>\n
Report 7: Last time through the loop.<\/strong><\/p>\n
As you can see by adjusting the @Skip<\/code> variable between each time through the loop the next set of 3 rows where displayed.<\/p>\n
A caveat: Changes to underlying data<\/h1>\n
One of the biggest concerns when paging data is that you are not holding any locks or version control of the results. Each execution of the SELECT<\/code> statement fetching rows is executing the query again. This could be an issue for hard to optimize query, but there is one more interesting issue with that. Changes to the results of your query.<\/p>\n
Aany change to the underlying data can cause you to see rows again, or perhaps miss rows. For example, consider the following set of statements in Listing 7:<\/p>\n
-- Display first 3 rows\r\nDECLARE @Skip INT = 0;\r\nDECLARE @Fetch INT = 3;\r\n\r\nSELECT * FROM TestData\r\nORDER BY ID OFFSET @Skip ROWS\r\n      FETCH NEXT @Fetch ROWS ONLY;\r\nGO<\/pre>\n
Listing 7: Showing the effect of changing rowsets<\/strong><\/p>\n
This returns the output you see in report 8:<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 8: Result from fetching first three rows<\/strong><\/p>\n
Next, in Listing 8, I will delete the row with ID = 3, and then run the statement that an application would execute if paging through these rows. It is the same code as in Listing 7, but I skipped 3 rows instead of 0.<\/p>\n
DELETE FROM TestData\r\nWHERE ID = 3;\r\nGO\r\n\r\n-- Display first 3 rows\r\nDECLARE @Skip INT = 3;\r\nDECLARE @Fetch INT = 3;\r\n\r\nSELECT * F\r\nROM TestData\r\nORDER BY ID OFFSET @Skip ROWS\r\n      FETCH NEXT @Fetch ROWS ONLY;<\/pre>\n
Listing 8: Removing an already fetched row, then fetching next rows<\/strong><\/p>\n
In report 9, you can see that the ID value starts at 5 instead of 4, like you may have expected. When rows are inserted, you may end up with the same row returned multiple times.<\/p>\n
\"Graphical<\/p>\n
Report 9: Shows that row with ID=4 has been skipped.<\/strong><\/p>\n
If you require to get absolutely all of the rows from your SELECT<\/code> statement, it can be useful to store the results in a temporary table and page through it. Another method of handling this is using SNAPSHOT<\/code> isolation level.<\/p>\n
Summary<\/h1>\n
In this article you learned how to use the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause to page through an ordered set of records. The OFFSET<\/code> option was used to skip a specific number of rows in the ordered set. Whereas the FETCH<\/code> option was used to identify the number of rows to FETCH<\/code> from the record set. By controlling these two different options a client application could programmatically page through the rows of data a page at a time. Next time you need to page through a set of rows in a table consider whether using the OFFSET<\/code> and FETCH<\/code> options of the ORDER BY<\/code> clause will meet your paging requirements.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Sometimes when working with very large sets of data, you may not want to return all of the data at once. I discussed using TOP in my previous article, which allowed you to only get a number of rows from the start of the results from a query. However, if you want to see the……<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525,143531],"tags":[],"coauthors":[11330],"class_list":["post-96331","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96331","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96331"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96331\/revisions"}],"predecessor-version":[{"id":97080,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96331\/revisions\/97080"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96331"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96331"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96331"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96331"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}