In SQL Server, how to find number of rows in a table without doing a count?

Question

I am working with SQL Server 2008 R2.

I have a table named punch with lot of rows. I want to know number of rows in this table but without doing a count. Is it possible? If yes then how?

Thanks

@vkp `count` is taking lot of time. I ran it and it was still not completed after 8 minutes so I killed it. There must be another easier way. — ChumboChappati, Nov 05 '15 at 21:32
Are there any transactions with locks on `punch` that might have your query on hold? — Bacon Bits, Nov 05 '15 at 21:34
@BaconBits might be the lock issue. this time i issued `count` with `(nolock)` and get the response in 2 minutes — ChumboChappati, Nov 05 '15 at 21:48

Juan Carlos Oropeza · Accepted Answer · 2015-11-05T21:44:57.910

3

Sql have a function for that

sp_spaceused <Tablename>;

edited Nov 05 '15 at 21:44

answered Nov 05 '15 at 21:36

Juan Carlos Oropeza

47,252
12
78
118

this tells me 204137424 rows in **punch** table – ChumboChappati Nov 05 '15 at 21:42
and what is the correct number or rows for punch? – Juan Carlos Oropeza Nov 05 '15 at 21:44
size of **punch**? I dont understand. Please explain. – ChumboChappati Nov 05 '15 at 21:47
how many rows you get if do `select count(*) from punch` – Juan Carlos Oropeza Nov 05 '15 at 21:47
204137424 rows I get – ChumboChappati Nov 05 '15 at 21:49
2

Then you are getting the right value isnt? what is the problem? isnt fast enough? – Juan Carlos Oropeza Nov 05 '15 at 21:50

score 1 · Answer 2 · answered Nov 05 '15 at 21:33

1

Here is one way.

select row_count
from sys.dm_db_partition_stats
where object_id = object_id('punch')

answered Nov 05 '15 at 21:33

Sean Lange

33,028
3
25
40

this gives me 9 rows. Do I need to add all the values in those 9 rows to know the total number of rows in that table? – ChumboChappati Nov 05 '15 at 21:40
Yes that view gives you the row count for every partition in the table. It is very clearly stated right there in the documentation. https://msdn.microsoft.com/en-us/library/ms187737.aspx – Sean Lange Nov 05 '15 at 21:43
The values I get are 72215111, 131922313, 204137424, 72215111, 131922313, 72215111, 131922313, 72215111, 131922313. If I add them up the total is **1020687120**. – ChumboChappati Nov 05 '15 at 21:44
This should be filtered by correct index_id – sepupic Jan 17 '20 at 14:45

In SQL Server, how to find number of rows in a table without doing a count?

2 Answers2