Why is ROW_NUMBER() not recognized in SQL Server 2008?

Question

Why is ROW_NUMBER() not recognized as a function name in 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.

Answer 1

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

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

Answer 2

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

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.

Answer 3

Extending the other 2 answers...

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

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

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:

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

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.

Answer 4

GBN - Love the mullet, man!