Consultes per obtenir paginació de resultats
SQLServer 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
SQL Server 2005 – 2008
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
SQL Server 2012 +
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
Referéncia: https://blog.sqlauthority.com/2017/02/26/pagination-sql-server-interview-question-week-111/