Equivalente de LIMIT e OFFSET para SQL Server?

No PostgreSQL, há as palavras-chave Limit e Offset que permitem uma paginação muito fácil dos conjuntos de resultados.

Qual é a syntax equivalente para o Sql Server?

O equivalente a LIMIT é SET ROWCOUNT , mas se você quiser paginação genérica, é melhor escrever uma consulta como esta:

 ;WITH Results_CTE AS ( SELECT Col1, Col2, ..., ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum FROM Table WHERE  ) SELECT * FROM Results_CTE WHERE RowNum >= @Offset AND RowNum < @Offset + @Limit 

A vantagem aqui é a parametrização do deslocamento e limite caso você decida alterar suas opções de paginação (ou permitir que o usuário faça isso).

Nota: o parâmetro @Offset deve usar uma indexação baseada em um para isso em vez da indexação baseada em zero normal.

Esse recurso agora é facilitado no SQL Server 2012. Isso está funcionando do SQL Server 2012 em diante.

Limite com deslocamento para selecionar de 11 a 20 linhas no SQL Server:

 SELECT email FROM emailTable WHERE user_id=3 ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; 
  • OFFSET : número de linhas ignoradas
  • NEXT : número necessário de próximas linhas

Referência: https://docs.microsoft.com/pt-br/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

 select top {LIMIT HERE} * from ( select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n from {YOUR TABLES} where {OTHER OPTIONAL FILTERS} ) xx where r_n_n >={OFFSET HERE} 

Uma observação: esta solução só funcionará no SQL Server 2005 ou superior, pois foi quando o ROW_NUMBER() foi implementado.

Você pode usar ROW_NUMBER em uma Expressão de Tabela Comum para conseguir isso.

 ;WITH My_CTE AS ( SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS row_number FROM My_Table WHERE < <>> ) SELECT col1, col2 FROM My_CTE WHERE row_number BETWEEN @start_row AND @end_row 

Outro exemplo:

 declare @limit int declare @offset int set @offset = 2; set @limit = 20; declare @count int declare @idxini int declare @idxfim int select @idxfim = @offset * @limit select @idxini = @idxfim - (@limit-1); WITH paging AS ( SELECT ROW_NUMBER() OVER (order by object_id) AS rowid, * FROM sys.objects ) select * from (select COUNT(1) as rowqtd from paging) qtd, paging where rowid between @idxini and @idxfim order by rowid; 

Há aqui alguém dizendo sobre esse recurso no sql 2011, é triste que eles escolhem uma palavra-chave pouco diferente “OFFSET / FETCH”, mas não é normal então ok.

Adicionando uma pequena variação na solução de Aaronaught, eu tipicamente parametrizo o número da página (@PageNum) e o tamanho da página (@PageSize). Dessa forma, cada evento de clique da página envia apenas o número da página solicitada junto com um tamanho de página configurável:

 begin with My_CTE as ( SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS row_number FROM My_Table WHERE < <>> ) select * from My_CTE WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1) AND @PageNum * @PageSize end 

O mais próximo que pude fazer é

 select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber and ct < = toNumber 

Eu acho que é similar a select * from [db].[dbo].[table] LIMIT 0, 10

Para mim, o uso de OFFSET e FETCH juntos foi lento, então usei uma combinação de TOP e OFFSET assim (que foi mais rápido):

 SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename WHERE  ORDER BY columname1 OFFSET 100 ROWS) aliasname 

Nota: Se você usar TOP e OFFSET juntos na mesma consulta, como:

 SELECT TOP 20 columname1, columname2 FROM tablename WHERE  ORDER BY columname1 OFFSET 100 ROWS 

Então você recebe um erro, então para usar TOP e OFFSET juntos, você precisa separá-lo com uma subconsulta.

E se você precisar usar SELECT DISTINCT, a consulta é como:

 SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2 WHERE  ORDER BY columname1 OFFSET 100 ROWS) aliasname 

Nota: O uso de SELECT ROW_NUMBER com DISTINCT não funcionou para mim.

 @nombre_row :nombre ligne par page @page:numero de la page //--------------code sql--------------- declare @page int,@nombre_row int; set @page='2'; set @nombre_row=5; SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY etudiant_ID ) AS RowNum, * FROM etudiant ) AS RowConstrainedResult WHERE RowNum >= ((@page-1)*@nombre_row)+1 AND RowNum < ((@page)*@nombre_row)+1 ORDER BY RowNum 

Já que ninguém forneceu este código ainda:

 SELECT TOP @limit f1, f2, f3... FROM t1 WHERE c1 = v1, c2 > v2... AND t1.id NOT IN (SELECT TOP @offset id FROM t1 WHERE c1 = v1, c2 > v2... ORDER BY o1, o2...) ORDER BY o1, o2... 

Pontos importantes:

  • ORDER BY deve ser idêntico
  • @limit pode ser substituído pelo número de resultados para recuperar,
  • @offset é o número de resultados a serem ignorados
  • Por favor, compare o desempenho com as soluções anteriores, pois elas podem ser mais eficientes
  • essa solução duplica where e order by cláusulas e fornecerá resultados incorretos se eles estiverem fora de sincronia
  • por outro lado, a order by está lá explicitamente se é isso que é necessário
 -- @RowsPerPage can be a fixed number and @PageNumber number can be passed DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2 SELECT * FROM MemberEmployeeData ORDER BY EmployeeNumber OFFSET @PageNumber*@RowsPerPage ROWS FETCH NEXT 10 ROWS ONLY 

No SQL Server você usaria TOP junto com ROW_NUMBER ()

 select top (@TakeCount) * --FETCH NEXT from( Select ROW_NUMBER() OVER (order by StartDate) AS rowid,* From YourTable )A where Rowid>@SkipCount --OFFSET