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 :(
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are actually three 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:
SELECT MAX(Row) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) Row FROM USERS) UserQuery
The third problem is that
count() is a much better way as expertly described in the answer by gbn.
Note also that
ROW is in the Reserved Keywords List, so that should be avoided as well.
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(*)
There's another variant, without scanning the table, using system tables:
Select si.RowCnt from sys.objects so join sysindexes si on si.id = so.object_id where so.type_desc = 'USER_TABLE' and si.indid in (0,1) -- heap or clustered index and so.Name in ('Users')
Compare plans, durations, time.. You'll see it's a faster variant.