SQL count rows in a table

Question

I need to send a SQL query to a database that tells me how many rows there are in a table. I could get all the rows in the table with a SELECT and then count them, but I don't like to do it this way. Is there some other way to ask the number of the rows in a table to the SQL server?

Does this answer your question? [Fastest way to count exact number of rows in a very large table?](https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table) — Liam, Jul 10 '20 at 10:31

score 187 · Accepted Answer · answered Mar 07 '15 at 16:13

187

Yes, SELECT COUNT(*) FROM TableName

answered Mar 07 '15 at 16:13

CyberDude

8,541
5
29
47

2

wouldn't it be more computationally intensive and more inefficient to select EVERY column instead of a single column??? – oldboy Mar 30 '18 at 22:15
3

No, the optimizer is smart enough to recognize this situation and generate the proper execution plan. Look it up, there are plenty of questions on the subject on SO. – CyberDude Apr 15 '18 at 08:56
3

i got such a huge list of things to do and thus no time to read about it right now unfortunately. this will go on the list of things to do, however – oldboy Apr 17 '18 at 17:50
9

this is not the best answer as it places unnecesary locks and pressure on production systems especially for large tables. Use sys.partitions instead to return the exact number of rows from table metadata directly! – Tarek Salha Jun 15 '20 at 03:52
1

This was failing for me because I, out of habit with aggregation functions, threw in a "GROUP BY" statement - don't do that! – PoloHoleSet Sep 22 '20 at 14:29

score 57 · Answer 2 · answered Mar 07 '15 at 17:23

57

select sum([rows])
from sys.partitions
where object_id=object_id('tablename')
 and index_id in (0,1)

is very fast but very rarely inaccurate.

answered Mar 07 '15 at 17:23

benjamin moskovits

5,261
1
12
22

2

I ran this against a table with 400+ million rows. Super quick. – Trevor Nestman Aug 29 '17 at 22:34
3

Extremely quick- less than a second with over 1.7Billion rows. Thanks! – mts396 Jan 10 '19 at 11:15
this is slower than select count(*) from table – Furkan Gözükara Jan 23 '19 at 07:40
1

This is a meta operation (which means that it reads a single (or just the total of all the rows describing partitions which is usually 1 entry) so it is almost always faster then reading each row of a table. – benjamin moskovits Dec 16 '19 at 16:00
3

This should have been the best answer. I have bench marked this on several tables of varying sizes and found using the metadata query is as fast or significantly faster than using ```SELECT(*)```. – Jim Jun 24 '20 at 13:30
What is the difference between "sys.partitions" and "sys.dm_db_partition_stats" used in Dustin's answer? – srm Dec 15 '21 at 17:38
Run this in SQLite, got: SQL error or missing database (no such table: sys.partitions). – Jia Gao Feb 11 '22 at 03:42
This is a SQL Server forum. Many of the solutions found here will probably not work on SQLLite. – benjamin moskovits Feb 13 '22 at 18:06
Does this require admin-rights on db? – quest Mar 08 '22 at 11:19

score 10 · Answer 3 · edited Jul 10 '20 at 10:29

10

Here is the Query

select count(*) from tablename

or

select count(rownum) from studennt

edited Jul 10 '20 at 10:29

Liam

27,717
28
128
190

answered Mar 01 '17 at 18:14

Lova Chittumuri

2,994
1
30
33

score 7 · Answer 4 · edited Dec 15 '21 at 17:36

7

Why don't you just right click on the table and then properties -> Storage and it would tell you the row count. You can use the below for row count in a view. Use your table's name in the WHERE clause.

SELECT SUM (row_count) 
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID('TableName')    
AND (index_id=0 or index_id=1)`

edited Dec 15 '21 at 17:36

srm

3,062
16
30

answered Dec 13 '17 at 23:18

Dustin

81
1
1

What is the difference between "sys.dm_db_partition_stats" and "sys.partitions" used in benjamin's answer? – srm Dec 15 '21 at 17:37

score 6 · Answer 5 · answered Apr 25 '15 at 06:17

Use This Query :

Select
    S.name + '.' + T.name As TableName ,
    SUM( P.rows ) As RowCont 

From sys.tables As T
    Inner Join sys.partitions As P On ( P.OBJECT_ID = T.OBJECT_ID )
    Inner Join sys.schemas As S On ( T.schema_id = S.schema_id )
Where
    ( T.is_ms_shipped = 0 )
    AND 
    ( P.index_id IN (1,0) )
    And
    ( T.type = 'U' )

Group By S.name , T.name 

Order By SUM( P.rows ) Desc

score 0 · Answer 6 · answered Jan 21 '20 at 21:50

The index statistics likely need to be current, but this will return the number of rows for all tables that are not MS_SHIPPED.

select o.name, i.rowcnt 
from sys.objects o join sys.sysindexes i 
on o.object_id = i.id
where o.is_ms_shipped = 0
and i.rowcnt > 0
order by o.name

score 0 · Answer 7 · answered Feb 13 '23 at 06:40

try this for all tables also can apply conditions:

SELECT SCHEMA_NAME([schema_id])[Schema],t.[name][ObjectName],i.[rowcnt][RowsCount] FROM sys.tables AS t
INNER JOIN sysobjects AS o ON o.[id]=t.[object_id]
INNER JOIN sysindexes AS i ON i.[id]=o.[id]
WHERE SCHEMA_NAME([schema_id])='dbo'
AND i.[indid]<2  
AND OBJECTPROPERTY(o.[id], 'IsMSShipped')=0
ORDER BY i.[rowcnt]

SQL count rows in a table

7 Answers7

Linked

Related