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

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.