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?

Sclerenchyma

Does this answer your question? Fastest way to count exact number of rows in a very large table? – Task

Answer 1

Yes, SELECT COUNT(*) FROM TableName

Vicissitude

wouldn't it be more computationally intensive and more inefficient to select EVERY column instead of a single column??? – Abortion

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. – Vicissitude

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 – Abortion

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! – Beestings

This was failing for me because I, out of habit with aggregation functions, threw in a "GROUP BY" statement - don't do that! – Glaciology

Answer 2

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.

Pratt

I ran this against a table with 400+ million rows. Super quick. – Genevieve

Extremely quick- less than a second with over 1.7Billion rows. Thanks! – Politico

this is slower than select count(*) from table – Blanket

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. – Pratt

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(*). – Hurl

What is the difference between "sys.partitions" and "sys.dm_db_partition_stats" used in Dustin's answer? – Grizzly

Run this in SQLite, got: SQL error or missing database (no such table: sys.partitions). – Gonta

This is a SQL Server forum. Many of the solutions found here will probably not work on SQLLite. – Pratt

Does this require admin-rights on db? – Langsdon

Answer 3

Here is the Query

select count(*) from tablename

or

select count(rownum) from student

Periderm

Answer 4

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)`

Vivianna

What is the difference between "sys.dm_db_partition_stats" and "sys.partitions" used in benjamin's answer? – Grizzly

Answer 5

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

Vedis

Answer 6

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

Odeen

Answer 7

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]

Skirr