How to know the total number or rows of a table

I am developing an application on C#. Therefore I need to know the total number of rows in the table to develop the application. I know to return the total number of columns but not rows. Please help me to do this.

more ▼

asked Apr 17, 2012 at 07:20 AM in Default

avatar image

10 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As well as the solution that @Rafat suggests, you can also find out the row count without even hitting the table:

 DECLARE @TableName varchar(255) = 'MyTable';
         SUM(p.row_count) AS rows
 FROM    sys.indexes AS i
 JOIN    sys.dm_db_partition_stats AS p ON i.OBJECT_ID = p.OBJECT_ID
                                           AND i.index_id = p.index_id
         AND i.index_id IN (0, 1)

This solution will be ultra fast, even on a table with billions of rows. With such large tables a SELECT COUNT(*) can take a long time, whereas querying the meta-data will be almost instantaneous.

more ▼

answered Apr 17, 2012 at 07:43 AM

avatar image

26.2k 18 38 48

@imteyazkhan374 I fully agree with William's point. That is much faster than the COUNT. But a tiny warning... the DMV is susceptible to small inaccuracies due to in-flight transactions.

Apr 17, 2012 at 08:04 AM Sacred Jewel

@Sacred Jewel - you are right about the DMV being susceptible to in-flight transactions, but the same can be said about COUNT. Both are only a snapshot in time, as any change that occurs after the query is done will not appear.

I tend to use the DMV more for tables that are large or have high transaction volumes on them. If the table is being hit by lots of changes, then I certainly don't want to potentially be blocking it just to get a count.

That said, it depends on the use case. Maybe I want/need to block changes, then @Rafat's solution would be better (and may even require query hints / different isolation level).

Apr 17, 2012 at 08:29 AM WilliamD

@William I agree to all your points except one. I do not think that COUNT is succeptible to in-flight transactions as much as the DMV we are talking about. SELECT COUNT will ask for a shared lock, hence I could be more sure of the results as compared to the DMV's results. I see the DMV as the faster version of COUNT WITH (NOLOCK), obviously they are not the same. An easy example for that is a DELETE transaction in process and we execute both COUNT and DMV scripts. The DMV would give rows count instantly which at the end of the transaction may be far different than actual (COUNT WITH NOLOCK would have the same behavior). With COUNT, I would get the delay but will get more consistent results.

I am not against the usage, neither I have anything to correct you on, but as you said, both have their own pros & cons and should be used wisely.

Apr 17, 2012 at 11:12 AM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

You can easily achieve this with the use of COUNT function

more ▼

answered Apr 17, 2012 at 07:37 AM

avatar image

Sacred Jewel
1.7k 3 7 10

(comments are locked)
10|1200 characters needed characters left

The output of

exec sp_spaceused 'MyTable'

shows the number of rows.

more ▼

answered Apr 17, 2012 at 10:25 AM

avatar image

905 60 64 68

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 17, 2012 at 07:20 AM

Seen: 2270 times

Last Updated: Apr 17, 2012 at 11:12 AM

Copyright 2018 Redgate Software. Privacy Policy