SQL Server 2008

Question

I have this query from mysql

SELECT * 
FROM table 
LIMIT 100, 200

Now how do I write similar query in SQL Server 2008. Basically I'm using php and SQL Server as backend and need to display records in range (1-100 then 100-200)

Not 100% familiar with `mysql` so not making this an answer, but wouldn't you just use `select top 100 * from table` to retrieve the first 100 records? — Dave, Sep 25 '18 at 16:08
You need to use something to define the order. And paging can be done a number of ways. — Sean Lange, Sep 25 '18 at 16:10
Possible duplicate of [mysql limit x, y equivalent for SQL Server l?](https://stackoverflow.com/questions/6578643/mysql-limit-x-y-equivalent-for-sql-server-l) — Eric Brandt, Sep 25 '18 at 16:35
I flagged this as a possible dupe because you're using SQL Server 2008. If you had 2012+, you'd have OFFSET FETCH available to you, which would be a different thing. — Eric Brandt, Sep 25 '18 at 16:36
You know that Sql Server 2008 reaches end of life in less than 10 months, right? After that time, no new updates will be released... not even critical security patches. It's time to think about upgraded that server. — Joel Coehoorn, Sep 25 '18 at 18:38
Also, the MySql `LIMIT` syntax is not part of the ansi standard. — Joel Coehoorn, Sep 25 '18 at 18:39

score 2 · Answer 1 · edited Sep 25 '18 at 18:31

2

Try this:

SELECT * 
FROM 
    (SELECT 
         SomeColumn, ...,
         ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNumber 
     FROM 
         table) Aux
WHERE 
    RowNumber >= @start AND RowNumber < (@start + @length)

Notes: You need mandatory order by a column and also you have to make a subquery to get access to RowNumber in the WHERE clause

edited Sep 25 '18 at 18:31

marc_s

732,580
175
1,330
1,459

answered Sep 25 '18 at 16:39

ThorPF

53
6

Huh. For some reason I thought 2012 was the first version with windowing functions, but a check of the docs shows 2008 had them, too. – Joel Coehoorn Sep 25 '18 at 18:40

score 0 · Answer 2 · answered Sep 25 '18 at 21:50

You can try this approach:

SELECT ID, ItemID, ItemName, UnitCost, UnitPrice
FROM
(
SELECT     ID, ItemID, ItemName, UnitCost, UnitPrice,
ROW_NUMBER() OVER (ORDER BY UnitCost) AS Seq
FROM         dbo.Inventory
)t
WHERE Seq BETWEEN 100 AND 200

you will basically use row_number to fetch they information that you need.

SQL Server 2008

2 Answers2