{"id":546,"date":"2019-08-09T12:19:07","date_gmt":"2019-08-09T06:49:07","guid":{"rendered":"https:\/\/techieshouts.com\/?p=546"},"modified":"2022-08-09T19:06:31","modified_gmt":"2022-08-09T13:36:31","slug":"pagination-in-sql-server","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/pagination-in-sql-server\/","title":{"rendered":"Pagination in SQL Server"},"content":{"rendered":"\n

In this article, let\u2019s see various methods to implement pagination in SQL server. <\/p>\n\n\n\n

In SQL server database, a table can have N no of records. Populating all these records in a grid control slows down the web page. You need to divide the number of records and populate it in the web page. Pagination<\/a> is the process of splitting the number of records from a SELECT query result set into several pages. Based on the SQL server version, it can be implemented using ROW_NUMBER function or OFFSET FETCH clause.<\/p>\n\n\n\n

Let’s\ncreate a sample table with some dummy data to see the implementation of\npagination.<\/p>\n\n\n\n

Create table with dummy data<\/strong><\/h2>\n\n\n\n

Create\na table ‘Course’ and insert dummy data using the below queries<\/p>\n\n\n\n

CREATE TABLE Course\n(\n\tid INT PRIMARY KEY IDENTITY,\n\tcourseName VARCHAR(50) NOT NULL,\n\tdepartment VARCHAR(50) NOT NULL\n);\n\nDECLARE @count INT = 1, @depCount INT = 1\nWHILE (@count <= 200)\nBEGIN\n\tINSERT INTO Course VALUES ('Course ' + LTRIM(@count), 'Department ' + LTRIM(@depCount))\n\tSET @count += 1;\n\tSET @depCount += 1;\n\tIF(@depCount % 10 = 0)\n\t\tSET @depCount = 1\nEND;<\/pre>\n\n\n\n
Now, the course table contains 200 records.<\/p>\n\n\n\n
Pagination using ROW_NUMBER function<\/strong><\/h2>\n\n\n\n
ROW_NUMBER function is introduced in SQL Server 2005. Let’s see how pagination can be implemented using this function. This method can be used in SQL server version 2005 and above.<\/p>\n\n\n\n
DECLARE @PageNo INT = 1\nDECLARE @RowsPerPage INT = 10 --Records Per Page\n\nDECLARE @Start INT, @End INT --Start and end record number in a page\nSET @Start = (@PageNo -1) * @RowsPerPage + 1\nSET @End = @RowsPerPage * @PageNo\n\nSELECT * FROM \n(\n\tSELECT *, ROW_NUMBER() OVER(ORDER BY department, id) AS RowNum \n\tFROM Course\n) AS Programs\nWHERE RowNum BETWEEN @Start AND @End;<\/pre>\n\n\n\n
See the result below. It returns the first page with 10 records.<\/p>\n\n\n\n
\"Pagination
Pagination result – Page 1 shows first 10 records<\/figcaption><\/figure><\/div>\n\n\n\n
In the above script, change the value of the variable\n‘@PageNo’ to view various pages and the value of the variable ‘@RecordsPerPage’\nto show the records.<\/p>\n\n\n\n
DECLARE @PageNo INT = 3\nDECLARE @RowsPerPage INT = 15 --Records Per Page<\/pre>\n\n\n\n
See the result below after changing the variable values<\/p>\n\n\n\n
\"Pagination
Pagination result – Page 3 shows 15 records<\/figcaption><\/figure><\/div>\n\n\n\n
Pagination using OFFSET FETCH clause<\/strong><\/h2>\n\n\n\n
Another method to implement pagination in SQL server version 2012 or above is by using OFFSET FETCH clause. The Syntax of the OFFSET FETCH clause is <\/p>\n\n\n\n
SELECT columnName(s)\nFROM tableName\nORDER BY columnName\nOFFSET Rows_To_Skip\nFETCH NEXT Rows_To_Fetch ROWS ONLY;\n<\/pre>\n\n\n\n
The below query populates the first page with first 10 records.<\/p>\n\n\n\n
DECLARE @PageNo INT = 1\nDECLARE @RowsPerPage INT = 10 --Records Per Page\nDECLARE @RowsToSkip INT  =  (@PageNo -1) * @RowsPerPage\n\nSELECT * FROM Course \nORDER BY department, id \nOFFSET  @RowsToSkip ROWS\nFETCH NEXT @RowsPerPage ROWS ONLY;<\/pre>\n\n\n\n
And the result is,<\/p>\n\n\n\n
\"Pagination
Pagination result using OFFSET FETCH clause<\/figcaption><\/figure><\/div>\n\n\n\n
Among the above mentioned methods, it is recommended to use the OFFSET FETCH method to implement pagination in SQL server 2012 and above for better performance.<\/p>\n\n\n\n\n\n
Also, check “Generate comma-separated string from a table<\/a>“<\/p>\n","protected":false},"excerpt":{"rendered":"
In this article, let\u2019s see various methods to implement pagination in SQL server. In SQL server database, a table can have N no of records. Populating all these records in a grid control slows down the web page. You need to divide the number of records and populate it in the web page. Pagination is\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10,15],"tags":[50,39,40,51,41],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/546"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=546"}],"version-history":[{"count":8,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/546\/revisions"}],"predecessor-version":[{"id":593,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/546\/revisions\/593"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}