Mysql Limit Equivalent in SQL Server

Question

Someone knows how to relate the differences between Mysql and SQL Server, including using LIMIT n,n? Or if you have a link that lists the differences would be nice.

Example

SELECT * FROM tabela LIMIT 50, 100

How would be the equivalent in SQL Server?

Answer 1

If you are using Sqlserver 2012 you can use OFFSET and FETCH

SELECT * FROM Tabela 
ORDER BY coluna1
OFFSET 50 ROWS FETCH NEXT 100 ROWS ONLY  

Note:
OFFSET and FETCH may only be used in conjunction with ORDER BY

See OFFSET FETCH CLAUSE

Answer 2

As of SQL Server 2005, you can also use ROW_NUMBER:

SELECT TOP (100) *
  FROM (
       SELECT row_number() OVER (ORDER BY coluna1 ASC) AS row_number, *
       FROM Tabela
       ) TabelaNumerada
 WHERE row_number > 50

better still filtering with BETWEEN:

SELECT *
  FROM (
       SELECT row_number() OVER (ORDER BY coluna1 ASC) AS row_number, *
       FROM Tabela
       ) TabelaNumerada
 WHERE row_number BETWEEN 51 AND 150

Answer 3

I’m going to put another way, no group by, no temporary tables, just a simple select. The query below has the same result of the "limit 50, 100", that is brings between the record 51 until the 100.

SELECT TOP 50 *
FROM tabela 
WHERE not ID in ( 
  SELECT TOP 50 ID
  FROM   tabela
  ORDER BY ID ASC
) a 
ORDER BY ID ASC;

Answer 4

I did a quick and functional gambiarra:

I created a VIEW with SELECT below without conditioning Where:

SELECT row_number() OVER (ORDER BY p.idprod ASC) as paginacao, [demaiscampos] FROM [tabela]

Then I called the view using:

SELECT paginacao, [demaiscampos] FROM [tabela]  WHERE paginacao BETWEEN [reginicial] AND [regfinal]

This way I can page the query without overloading the bank’s processes.

There is also the method:

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 5

SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS NUMBER,
                    ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE
               ) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY ID_EXAMPLE

and also this:

--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

Answer 5

A solution equivalent to Mysql that does not require ORDER BY and that works in any version from SQL Server 2000 is:

SELECT  TOP 100 *
FROM    Tabela 
WHERE   Coluna1 NOT IN (SELECT TOP 50 Coluna1 FROM Tabela)

Remembering that 50 refers to the amount of lines that will be skipped/ignored and 100 the number of rows returned.

In SQL Server 2005/2008 another option is to use CTE:

WITH CTE AS
(
    SELECT  *, ROW_NUMBER() OVER (ORDER BY Coluna1) AS Linha
    FROM    Tabela 
)
SELECT  *
FROM    CTE
WHERE   Linha BETWEEN 51 AND 150

If order and version are not an issue the command LIMIT 50, 100 of Mysql is equivalent to [OFFSET] 50 ROWS FETCH ONLY 100 ROWS ONLY of SQL Server 2012 as already quoted.