Question: How to do Pagination in SQL Server?
Answer: A very popular question during interviews, which often stumps the candidate.
I have helped many organizations to hire the right talent for many years and I know that this one question often throws off DBA and developers.
The very common practice of the developers is to retrieve all the data in the page and then query each data one at a time. This is not efficient at all and creates lots of unnecessary load on the server. Let us see a method page pagination in SQL Server.
My preferred method of pagination is listed in the SQL Server 2012+ section as that is the most efficient in terms of performance.
Pagination in SQL Server 2000
USE AdventureWorks GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 120 SET @EndRow = 140 CREATE TABLE #tables (RowNumber INT IDENTITY(1,1), FirstName VARCHAR(100), LastName VARCHAR(100), EmailAddress VARCHAR(100)) INSERT INTO #tables (FirstName, LastName, EmailAddress) SELECT PC.FirstName, PC.LastName, PC.EmailAddress FROM Person.Contact PC ORDER BY FirstName, LastName, EmailAddress SELECT FirstName, LastName, EmailAddress FROM #tables WHERE RowNumber > @StartRow AND RowNumber < @EndRow DROP TABLE #tables GO
Pagination in SQL Server 2005
USE AdventureWorks GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 120 SET @EndRow = 140 SELECT FirstName, LastName, EmailAddress FROM ( SELECT PC.FirstName, PC.LastName, PC.EmailAddress, ROW_NUMBER() OVER( ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber FROM Person.Contact PC) PersonContact WHERE RowNumber > @StartRow AND RowNumber < @EndRow ORDER BY FirstName, LastName, EmailAddress GO
Pagination in SQL Server 2012 / 2014 / 2016 onwards
USE AdventureWorks2008R2 GO DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5 SELECT * FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID OFFSET @PageNumber*@RowsPerPage ROWS FETCH NEXT 10 ROWS ONLY GO
Here are a few additional articles you can read to strengthen your understanding.
- SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server
- SQL SERVER – Server Side Paging in SQL Server 2012 Performance Comparison
- SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
- SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005
- SQL SERVER – Server Side Paging in SQL Server 2012 – A Better Alternative
- SQL SERVER – Row Offset in SQL Server For Different Version
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
For 5th page records, may be you need to set offset as (@pagenumber-1)*@rowsperpage
Thanks Pinal .But what about the maximum rows that can be retrieved from Offset Fetch COmmand
As many as you want Raj!
Just want to highlight the fact that, Fetch Next accepts variables too.
So we could change
FETCH NEXT 10 ROWS ONLY
to
FETCH NEXT @RowsPerPage ROWS ONLY
so the code is more reusable.
Thanks Nazer.
If I have posts and comments and I whant 10 posts after the first 30 and for each post first 3 comments after first 9, how would I write my sql ?