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

Equivalent to MySQL LIMIT Clause for SQL Server

Many developers have sought alternatives to the LIMIT clause for SQL Server, as it remains a notable absence in the platform. To address this issue, let's delve into the available workarounds.

For SQL Server 2012 onwards, the OFFSET/FETCH syntax, part of the ANSI standard, offers a solution:

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

Prior to SQL Server 2012, the ROW_NUMBER() method can be employed, as described in this article.

However, if SQL Server 2012 is not available, a workaround involving a common table expression (CTE) can be used:

;WITH o AS
(
    SELECT TOP ((@start_from - 1) + @items_on_page)
         -- Again, this calculation depends on the method used for @start_from
      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;

This is one of several methods that can be applied to achieve the desired result. It's important to review the available documentation and consider the efficiency of the chosen approach.

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