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)
Incorrect syntax near ')'.
I don't get it :(
There are actually two problems with the query. The first is Max('Row') will return the string 'Row'. The Second is your subquery needs an alias.
Try like this:
I guess there are actually 3 problems with this query :). The 3rd being, count() is a much better way as expertly described by @gbn.
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
See this on SO too for some more info: http://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(*)
There's another variant, without scanning the table, using system tables:
Compare plans, durations, time.. You'll see it's a faster variant.
Change you query to :
Try this mate.