Limit and Offset for Web Paging in SQL Server

Question

I am working with PHP and have about 8000 records currently on DB.

As it is for a web page, I need to make a pagination to be able to read the data.

From what I understand, I have to do something similar to limit and offset to show from 50 to 50 pages, but don’t know how to do this with SQL Server.

I need it to work with Join below:

select  os.id, 
        os.dataHora, 
        material.nome as nomeEquip, 
        cliente.nomeFantasia, 
        setor.nome as NomeSetor, 
        os.motivoOs, 

(select sum(itemMaterial.valorUnitario) from os as OS1 inner join
             itemMaterial on itemMaterial.id = os.idItemMaterial 
where OS1.id = os.id ) as TotalMaterial, 

tipoOs.nome as NomeTipoOS, itemMaterial.nSerie, itemMaterial.rm, os.status from os 
inner join itemMaterial on itemMaterial.id = os.idItemMaterial 
inner join modelo on modelo.id = itemMaterial.idModelo 
inner join material on material.id = itemMaterial.idMaterial 
inner join cliente on cliente.id = os.idCliente 
inner join setor on setor.id = os.idSetor 
inner join usuario on usuario.id = os.idUsuarioSolicitante 
inner join tipoOs on tipoOs.id = os.idTipoOs 

where cliente.id = (select usuario.idCliente from usuario where usuario.login = 'julio') 
order by dataHora desc

GIST

How to proceed in this case?

Answer 1

As of SQL 2012

SELECT coluna1,
       ...
  FROM tabela
 WHERE ...
 ORDER BY coluna1,
          ...
 OFFSET @Offset ROWS
 FETCH NEXT @Limit ROWS ONLY;

In your case you can change the variable @Limit for @QtdPorPagina and @Offset for (@Pagina - 1) * @QtdPorPagina and use as follows:

DECLARE @QtdPorPagina INT = 50,
        @Pagina       INT = 1;

Replacing in your query

DECLARE @QtdPorPagina INT = 50,
        @Pagina       INT = 1;

SELECT os.id,
       os.dataHora,
       material.nome AS nomeEquip,
       cliente.nomeFantasia,
       setor.nome AS NomeSetor,
       os.motivoOs,
       (SELECT SUM(itemMaterial.valorUnitario)
          FROM os AS OS1
               INNER JOIN itemMaterial ON itemMaterial.id = os.idItemMaterial
         WHERE OS1.id = os.id ) AS TotalMaterial,
       tipoOs.nome AS NomeTipoOS,
       itemMaterial.nSerie,
       itemMaterial.rm,
       os.status
  FROM os
       INNER JOIN itemMaterial ON itemMaterial.id = os.idItemMaterial
       INNER JOIN modelo ON modelo.id = itemMaterial.idModelo
       INNER JOIN material ON material.id = itemMaterial.idMaterial
       INNER JOIN cliente ON cliente.id = os.idCliente
       INNER JOIN setor ON setor.id = os.idSetor
       INNER JOIN usuario ON usuario.id = os.idUsuarioSolicitante
       INNER JOIN tipoOs ON tipoOs.id = os.idTipoOs
 WHERE cliente.id = (SELECT usuario.idCliente
                       FROM usuario
                      WHERE usuario.login = 'julio')
 ORDER BY dataHora DESC
OFFSET (@Pagina - 1) * @QtdPorPagina ROWS
 FETCH NEXT @QtdPorPagina ROWS ONLY;

---

For SQL Server before 2012

To get a similar result you can use the function ROW_NUMBER()to generate the line number and work with that information as follows:

WITH resultado AS
(SELECT coluna1,
        ...,
        ROW_NUMBER() OVER (ORDER BY coluna1, ...) AS linha
   FROM tabela
  WHERE ...)
SELECT *
  FROM resultado
 WHERE linha >= @Offset
   AND linha < @Offset + @Limit

---

Similar question from Soen: Equivalent of LIMIT and OFFSET for SQL Server?