how to count number of records in table

[chithra r]

hi all can anyone tell me how to count the number of records(rows)in a table without using "COUNT" aggregate function. thanx in advance.

pintoo

[Joe 2]

chithra.r wrote:

without using "COUNT"

What is that you are trying to do? If you are writing a proc, you may use @@RowCount[^] Pl: Read through the documentation listed in the above URL, to know the scenarios where it can be applied.

Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime. Pradeep Joe

[Ashfield]

@@Rowcount is only gives you the number of rows affected by the last query, for example select count(*) from table1 will give an @@RowCount of 1 The best way I know of without using select count(*) is EXEC sp_spaceused 'tablename' which gives you name rows reserved data index_size unused If you only want an approximate count select max(rows) from sysindexes where indid < 2 and id = object_id('TableName') (this is not 100% accurate as rows sysindexes is not always updated)

Bob Ashfield Consultants Ltd

[Paddy Boyd]

What's wrong with COUNT()?

[GuyThiebaut]

I wonder if you are misunderstanding what you can do with COUNT Try this query and see if the results it gives you are what you want:

select 'Rows in table:'+cast((select count(*) from tablename)as varchar(20))

You always pass failure on the way to success.

[Chris Meech]

This might work instead

Select MAX(rowid)-MIN(rowid) from table

Chris Meech I am Canadian. [heard in a local bar] Donate to help Conquer Cancer[^]