为什么在SQL Server 2008中无法识别ROW_NUMBER（）？

Why is ROW_NUMBER() not recognized as a function name in SQL Server 2008?

为什么ROW_NUMBER（）在SQL Server 2008中不被识别为函数名？

I try this

我试试这个

SELECT 
    ROW_NUMBER() AS Row, Lname
FROM MEN
GO

and I get this error:

我收到此错误：

Msg 195, Level 15, State 10, Line 1 'ROW_NUMBER' is not a recognized function name.

消息195，级别15，状态10，行1'ROW_NUMBER'不是可识别的函数名称。

4 个解决方案

#1

You appear to be using the wrong syntax. Here is an example using the AdventureWorks database.

您似乎使用了错误的语法。以下是使用AdventureWorks数据库的示例。

select 
    row_number() over(order by Name),   
    Name
from HumanResources.Department

#2

Extending the other 2 answers...

扩展其他2个答案......

I've tried the exact same command on SQL 2005 with 2 databases.

我在SQL 2005上用2个数据库尝试了完全相同的命令。

For both compatibility levels 80 and 90, the error is:

对于兼容级别80和90，错误是：

Msg 1035, Level 15, State 10, Line 2
Incorrect syntax near 'ROW_NUMBER', expected 'OVER'.

I can only generate this error on a SQL 2000 box:

我只能在SQL 2000框上生成此错误：

Msg 195, Level 15, State 10, Line 2
'ROW_NUMBER' is not a recognized function name.

What does SELECT @@version say? I'd make 100% sure that you are on the version you expect...

SELECT @@版本说什么？我百分百肯定你的版本是你期望的......

My other thought is compat level 65 which can't be set explicitly in SQL Server 2005 and above it seems. And I don't have any legacy databases lying around to test.

我的另一个想法是compat level 65，它无法在SQL Server 2005及其上面显式设置。我没有任何遗留数据库可供测试。

#3

Check your database compatibility; ensure that it's set to 90 or higher.

检查数据库兼容性;确保将其设置为90或更高。

It appears there are at least 2 things that are off the mark here.

看来这里至少有两件事是不合适的。

The syntax in your question is incorrect, but wouldn't be producing the unrecognized function error.
您的问题中的语法不正确，但不会产生无法识别的函数错误。
SQL 2005 and 2008 do support the ROW_NUMBER OVER() keywords/command. Perhaps are you using SQL 2008 Management Studio to connect to a SQL 2000 machine? Double check with SELECT @@Version that your DB is indeed a SQL 2008 DB.
SQL 2005和2008确实支持ROW_NUMBER OVER（）keywords /命令。也许您使用SQL 2008 Management Studio连接到SQL 2000计算机？请仔细检查SELECT @@ Version，确保您的数据库确实是SQL 2008 DB。

#4

If you're using SSMS that says SQL Sever 2008, doesn't necessarily mean that you're connect to the respective DB. Using @@version to check the version of the DB you're connected to because SQL 2005 uses : [ROW_NUMBER() OVER (ORDER BY ColName)]

如果您使用的是SSMS，它说的是SQL Sever 2008，并不一定意味着您将连接到相应的数据库。使用@@ version检查您连接的数据库的版本，因为SQL 2005使用：[ROW_NUMBER（）OVER（ORDER BY ColName）]

#1