How to use limit in Microsoft SQL Server 2008?

Question

We have to use Limit keyword in SQL server 2008/2012.

We need to apply limit for every query where start index will change every time. When I was googling found TOP but it won't work for us. Can anyone please share how to use LIMIT keyword in sql server where every time start index change.

We need query in SQL server like below -

SELECT * from STOCK LIMIT 11, 10000 (where 11=start index, 10000=size)

Answer 1

You could use a CTE with a window function, like in the answer to this SO question -> Skip first row in SQL Server 2005?

Answer 2

We have upgraded to SQL server 2012 and replaced query with OFFSET and FETCH. Sample is below.

SELECT ITEM_ID, PRICE FROM MENU ORDER BY ITEM_ID ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; 

Answer 3

You would have to use something like the ROW_NUMBER() function and then specify what you want from there.

select top 10000 *, row_number() over (order by [YourFieldName]) as row from Stock where row > 11

Answer 4

This is probably not a good long-term solution for you, but if the table has an Identity field you could do something like this:

SELECT TOP 1000 * FROM STOCK
WHERE Id > 11
ORDER BY Id