SQL Server fetch next x number of rows with where clause

Question

Microsoft SQL Server 2008 R2

I am running a large SQL select query that may take hours to complete. So I try to break the query results into smaller sets.

e.g return results 1-10,000 first, then 10,001 - 20000, and so on

I used below code, but it gave me error

SELECT * 
FROM PP_ConsolidatedSalesView
WHERE financial_period = '2018-11'
ORDER BY id
OFFSET 10000 ROWS
FETCH NEXT 10000 ROWS ONLY

I use a loop to dynamically change the offset and fetch next values.

The error message is:

Incorrect syntax near 'OFFSET'

Does anyone have an idea why? And is there an alternative solution?

What version are you using? Can you share the exact error message? — Mureinik, Dec 10 '18 at 05:53
Possible duplicate of [Incorrect syntax near OFFSET command](https://stackoverflow.com/questions/42263984/incorrect-syntax-near-offset-command) — Renz Dominique, Dec 10 '18 at 05:53
What SQL Server version are you running against? Run `SELECT @@VERSION` and see. `OFFSET . FETCH` was introduced in SQL Server **2012** and isn't available in earlier versions — marc_s, Dec 10 '18 at 05:58

score 1 · Accepted Answer · answered Dec 10 '18 at 05:53

1

Can you please confirm the database compatibility level. Offset is present in SQL Server 2012. If database is 2008 compatbility mode, then keyword isnt available.

You can check it like below:

USE AdventureWorks2012;  
 GO  

 SELECT compatibility_level  
 FROM sys.databases WHERE name = 'AdventureWorks2012';  
 GO

More info here: Incorrect syntax near OFFSET command

answered Dec 10 '18 at 05:53

Gauravsa

6,330
2
21
30

it is sql server 2008 R2 – ppau2004 Dec 10 '18 at 06:05

SQL Server fetch next x number of rows with where clause

1 Answers1