MySQL LIMIT clause equivalent for SQL SERVER

Solution 1:

In SQL Server 2012, there is support for the ANSI standard OFFSET / FETCH syntax. I blogged about this and here is the official doc (this is an extension to ORDER BY). Your syntax converted for SQL Server 2012 would be:

SELECT ID, Name, Price, Image 
  FROM Products 
  ORDER BY ID ASC 
  OFFSET (@start_from - 1) ROWS -- not sure if you need -1
    -- because I don't know how you calculated @start_from
  FETCH NEXT @items_on_page ROWS ONLY;

Prior to that, you need to use various workarounds, including the ROW_NUMBER() method. See this article and the follow-on discussion. If you are not on SQL Server 2012, you can't use standard syntax or MySQL's non-standard LIMIT but you can use a more verbose solution such as:

;WITH o AS
(
    SELECT TOP ((@start_from - 1) + @items_on_page)
         -- again, not sure if you need -1 because I 
         -- don't know how you calculated @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;

There are many other ways to skin this cat, this is unlikely to be the most efficient but syntax-wise is probably simplest. I suggest reviewing the links I posted as well as the duplicate suggestions noted in the comments to the question.

Solution 2:

For SQL Server 2005 and 2008 This is an example query to select rows from 11 to 20 from Report table ordered by LastName.

SELECT a.* FROM 
(SELECT *, ROW_NUMBER() OVER (ORDER BY LastName) as row FROM Report) a
WHERE a.row > 10 and a.row <= 20