x

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

imteyazkhan374 gravatar image

imteyazkhan374
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';

SELECT  OBJECT_NAME(i.OBJECT_ID) AS tableName,
        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
WHERE   OBJECT_NAME(i.OBJECT_ID) = @TableName
        AND i.index_id IN (0, 1)
GROUP BY OBJECT_NAME(i.OBJECT_ID)
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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@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

SELECT COUNT(*) FROM TABLE
more ▼

answered Apr 17, 2012 at 07:37 AM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

(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

xnl28 gravatar image

xnl28
895 56 60 61

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945

asked: Apr 17, 2012 at 07:20 AM

Seen: 1913 times

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