How to Replicate MySQL's LIMIT Clause in SQL Server?

SQL Server equivalent to MySQL's LIMIT clause

MySQL's LIMIT clause, which restricts the number of rows returned by a query, has no direct equivalent in SQL Server. This can be frustrating, but there are several workarounds available.

OFFSET / FETCH syntax (SQL Server 2012 and later)

In SQL Server 2012 and later, the ANSI standard OFFSET / FETCH syntax can be used for pagination. The following query demonstrates this syntax:

SELECT ID, Name, Price, Image 
FROM Products 
ORDER BY ID ASC 
OFFSET (@start_from - 1) ROWS
FETCH NEXT @items_on_page ROWS ONLY;

ROW_NUMBER() method (prior to SQL Server 2012)

Prior to SQL Server 2012, the ROW_NUMBER() method can be used to simulate the LIMIT clause:

WITH o AS
(
    SELECT TOP ((@start_from - 1) + @items_on_page)
      RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC)
      /* , other columns */
    FROM Products
)
SELECT 
    RowNum
    /* , other columns */
FROM
    o
WHERE
    RowNum >= @start_from
ORDER BY
    RowNum;

Other workarounds

In addition to the methods described above, there are various other workarounds available, including using cursors or temporary tables. However, these solutions are generally less efficient and more complex.

It's important to note that Microsoft has acknowledged the limitations of the current pagination mechanisms and has expressed interest in adding native LIMIT support to SQL Server in future releases.

The above is the detailed content of How to Replicate MySQL's LIMIT Clause in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!