Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so,

SELECT MAX('Row') FROM 

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)

Error Desc:

Incorrect syntax near ')'.

I don't get it :(

• If you want to get the exact count of rows in an efficient manner, then COUNT() is it. The ANSI standard (look for "Scalar expressions 125") states that COUNT() give the row count of a table: it is intended to be optimised from the start.

If COUNT(*) is specified, then the result is the cardinality of T.

-
A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it

-
SUM(1) may be optimised to COUNT(*) internally but I'd never use it

-
@@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.

If you can live with approximate for SQL Server then use sys.dm_db_partition_stats. Marian's answer is out of date now since SQL Server 2005 added dmvs

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

See this on SO too for some more info: https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table/6069288#6069288

In summary, there is exactly one useful way of getting the number of rows in a table. COUNT(*)

StackExchange Database Administrators Q#3849, answer score: 21