3

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 :(

14

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.

| improve this answer | |
21
  • 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(*)

| improve this answer | |
6

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.

| improve this answer | |
  • 3
    Presumably this does not give an exact count though? – Jack Douglas Jul 14 '11 at 9:20
-1

Change you query to :

SELECT MAX(A.Row) FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) as Row FROM Users) as A

Try this mate.

| improve this answer | |
-1

select si.rowcnt from sysindexes si where si.indid in (0,1) and si.id=Object_ID('Users')

| improve this answer | |
-1

Check This Simple Query :

select rows from sysindexes where Id = OBJECT_ID('TableName') and indid <2
| improve this answer | |
-2

This is faster

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Users')   
AND (index_id<2);
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.