How to know the total number or rows of a table

Question

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.

Answer 1

0

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.

answered Apr 17 '12 at 07:43 AM

WilliamD
25.3k ● 16 ● 18 ● 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 '12 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 '12 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 '12 at 11:12 AM Sacred Jewel

(comments are locked)

Answer 2

0

You can easily achieve this with the use of COUNT function

SELECT COUNT(*) FROM TABLE

answered Apr 17 '12 at 07:37 AM

Sacred Jewel
1.6k ● 2 ● 4 ● 5

(comments are locked)

Answer 3

0

The output of

exec sp_spaceused 'MyTable'

shows the number of rows.

answered Apr 17 '12 at 10:25 AM

xnl28
765 ● 45 ● 49 ● 56

(comments are locked)

How to know the total number or rows of a table

Follow this question

Related Questions