Incorrect syntax near OFFSET command

Question

Why this doesn't work and gives me a "Incorrect syntax near offset command" error

SELECT o.orderdate, o.orderid, o.empid, o.custid FROM Sales.Orders o
ORDER BY o.orderdate, o.orderid
OFFSET 50 ROWS
FETCH NEXT 25 ROWS ONLY;

I am using SQL Server Express 2014

Answer 1

Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available.

View or Change the Compatibility Level of a Database

In T-SQL you can check it like this:

 USE AdventureWorks2012;  
 GO  

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

Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?:

 65 - SQL Server 6.5
 70 - SQL Server 7.0
 80 - SQL Server 2000
 90 - SQL Server 2005
100 - SQL Server 2008/R2
110 - SQL Server 2012
120 - SQL Server 2014
130 - SQL Server 2016
140 - SQL Server 2017

---

Besides, Azure SQL Data Warehouse and Parallel Data Warehouse do not support OFFSET clause, as can be seen from the docs of the ORDER BY clause:

-- Syntax for SQL Server and Azure SQL Database  

ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]   
[ <offset_fetch> ]  

<offset_fetch> ::=  
{   
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }  
    [  
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY  
    ]  
}

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

[ ORDER BY   
    {  
    order_by_expression   
    [ ASC | DESC ]   
    } [ ,...n ]   
]

Answer 2

My problem was that I was attempting to use OFFSET on a view, but wasn't joining.

Problematic query:

declare @PageSize int = 25;
declare @PageNumber int = 1;

with countCte as 
(
    select count(*) as TotalCount from vw_viewName vn
    where 1=1
)
select * from vw_viewName vn
cross join countCte cou
where 1=1
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE)

Adding an order by fixed the problem:

where 1=1
order by vn.ID desc
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE)