Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 3 ways to get an accurate count

Author  Topic 

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-04-05 : 03:16:30
Hi all,

I want to know 3 ways to get an accurate count of the number of records in a table Using query analyzer?

i know only one i,e
select count(*) from tablename

what are the remaining 2 ways
i am beginner to sql server,any help ll be appreciated.
Thank's in advance




khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 03:26:00
this is the only other way that i know will give accurate count

select * from tablename
select @@rowcount



KH

Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-04-05 : 03:29:20
but it ll list all the records first,
i want the count only

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 03:42:04
Why do you need 3 ways to get accurate count of records in a table ?

Is this some sort of quiz or interview question ?


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 03:48:47
1) select sum(1) from table1
2) select count(*) from table1
3) update table1 set col1 = col1; select @@rowcount


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 03:56:20
quote:
Originally posted by Peso

1) select sum(1) from table1
2) select count(*) from table1
3) update table1 set col1 = col1; select @@rowcount


Peter Larsson
Helsingborg, Sweden


Good one


KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 04:11:17
quote:
Originally posted by Peso

1) select sum(1) from table1
2) select count(*) from table1
3) update table1 set col1 = col1; select @@rowcount


Peter Larsson
Helsingborg, Sweden


4 sp_spaceused table1
5 Select rows from sysindexes where id=object_id('table1')
where indid<2

Provided DBCC Updateusage is Run


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-04-05 : 05:52:14
Thank's Peso and Madhivanan

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 09:00:42
Are 4) and 5) accurate?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 23:46:57
quote:
Originally posted by Peso

Are 4) and 5) accurate?


Peter Larsson
Helsingborg, Sweden


Yes

Provided DBCC Updateusage is Run

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Ranjay Kumar
Starting Member

3 Posts

Posted - 2014-11-18 : 03:52:52
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
sp_spaceused 'Table Name'
select @@rowcount from 'Table name'


ranjay
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-11-26 : 03:31:27
quote:
Originally posted by Ranjay Kumar

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
sp_spaceused 'Table Name'
select @@rowcount from 'Table name'


ranjay


Looks like you have just posted answers already provided

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -