Why can't I query OFFSET/ FETCH query on my SQL Server?

Question

I want to run this query on my SQL Server as follows:

Microsoft SQL Server Management Studio 10.50.1600.1

But it can't recognize the OFFSET and so shows ERROR?

SELECT * FROM dbo.tbl_MatchDetail
ORDER BY MatchDetailID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

The syntax you show was apparently introduced in SQL Server 2012, so if you're using SQL Server 2008 per your tag, that's the problem. See [this question](http://stackoverflow.com/q/2135418/157957) for alternatives. — IMSoP, Nov 03 '13 at 19:14

score 25 · Accepted Answer · edited Nov 03 '13 at 20:06

25

OFFSET FETCH is a new feature added to Sql Server 2012 and does not exist in Sql Server 2008.

edited Nov 03 '13 at 20:06

Joel Coehoorn

answered Nov 03 '13 at 20:03

Amir Keshavarz

Microsoft SQL Server Management Studio 12.0.1524.0 This is my MSSQL Info as above with MSSQL 2014 CTP2 still OFFSET keyword is not valid Why? – Milson Nov 06 '13 at 16:50
OFFSET 10 ROWS FETCH NEXT 10 ROWS only. – Amir Keshavarz Nov 06 '13 at 18:16
1

Add Only keyword at the end. – Amir Keshavarz Nov 06 '13 at 18:16
1

@AmirrezaKeshavarz Does it work with SQL Server 2014 (12.0.2000.8)? I always get 'Incorrect syntax near 'OFFSET'' error – Arian Sharifian May 20 '14 at 18:35
@ArianSharifian having same issue in SSMS 2012 :/ – Tony Sep 11 '14 at 15:00
Check the only at the end , the syntax should be true . – Amir Keshavarz Sep 12 '14 at 18:13
Please Consider that it should appear after order by clause. – Amir Keshavarz Mar 16 '15 at 08:34

score 17 · Answer 2 · answered May 19 '15 at 10:05

17

Please be aware you will get an error even in 2014 if you don't have an order by. The offset must follow an order by statement.

answered May 19 '15 at 10:05

Dale Fraser

2 Answers2