OFFSET 命令附近的语法不正确 [英] Incorrect syntax near OFFSET command

问题描述

为什么这不起作用并给我一个偏移命令附近的语法不正确"错误

SELECT o.orderdate, o.orderid, o.empid, o.custid FROM Sales.Orders oORDER BY o.orderdate, o.orderid偏移 50 行仅提取接下来的 25 行；

我使用的是 SQL Server Express 2014

解决方案

检查数据库兼容级别.OFFSET 是在 SQL Server 2012 中加入的，所以如果你的数据库是 2008 兼容模式，这个关键字不可用.

查看或更改数据库的兼容性级别

在 T-SQL 中你可以这样检查:

 使用 AdventureWorks2012；走选择兼容性_级别FROM sys.databases WHERE name = 'AdventureWorks2012';走

这是取自 不推荐使用 sp_dbcmptlevel 后如何检查 SQL Server 数据库兼容性?:

 65 - SQL Server 6.570 - SQL Server 7.080 - SQL Server 200090 - SQL Server 2005100 - SQL Server 2008/R2110 - SQL Server 2012120 - SQL Server 2014130 - SQL Server 2016140 - SQL Server 2017

<小时>

此外，Azure SQL 数据仓库和并行数据仓库不支持 OFFSET 子句，从 ORDER BY 条款:

<块引用>

-- SQL Server 和 Azure SQL 数据库的语法ORDER BY order_by_expression[整理 collat​​ion_name ][ASC |说明][ ,...n ][ <offset_fetch>]<offset_fetch>::={偏移 { integer_constant |offset_row_count_expression } { ROW |行 }[取{第一个|下一个 } {整数常量 |fetch_row_count_expression } { ROW |仅行}]}

<块引用>

-- Azure SQL 数据仓库和并行数据仓库的语法[ 订购者{order_by_expression[ASC |说明]} [ ,...n ]]

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

解决方案

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 ]   
]

这篇关于OFFSET 命令附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！