MySQL LIMIT clause equivalent for SQL SERVER

Question

I've been doing a lot of reading on alternatives to the LIMIT clause for SQL SERVER. It's so frustrating that they still refuse to adapt it. Anyway, I really havn't been able to get my head around this. The query I'm trying to convert is this...

SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC LIMIT $start_from, $items_on_page

Any assistance would be much appreciated, thank you.

Answer 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.