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 &lt; 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(*)