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?
asked Mar 7, 2015 at 16:11
-
1Does this answer your question? Fastest way to count exact number of rows in a very large table?– LiamCommented Jul 10, 2020 at 10:31
Add a comment
|
8 Answers
Yes, SELECT COUNT(*) FROM TableName
-
2wouldn't it be more computationally intensive and more inefficient to select EVERY column instead of a single column???– oldboyCommented Mar 30, 2018 at 22:15
-
5No, 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. Commented Apr 15, 2018 at 8:56
-
3i 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– oldboyCommented Apr 17, 2018 at 17:50
-
11this 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! Commented Jun 15, 2020 at 3:52
-
2This was failing for me because I, out of habit with aggregation functions, threw in a "GROUP BY" statement - don't do that! Commented Sep 22, 2020 at 14:29
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 7, 2015 at 17:23
-
2I ran this against a table with 400+ million rows. Super quick. Commented Aug 29, 2017 at 22:34
-
3Extremely quick- less than a second with over 1.7Billion rows. Thanks!– MS-StackCommented Jan 10, 2019 at 11:15
-
1This 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. Commented Dec 16, 2019 at 16:00
-
3This 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(*).– JimCommented Jun 24, 2020 at 13:30 -
1This is a SQL Server forum. Many of the solutions found here will probably not work on SQLLite. Commented Feb 13, 2022 at 18:06
Here is the Query
select count(*) from tablename
or
select count(rownum) from student
answered Mar 1, 2017 at 18:14
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)`
-
2What is the difference between "sys.dm_db_partition_stats" and "sys.partitions" used in benjamin's answer?– srmCommented Dec 15, 2021 at 17:37
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
answered Apr 25, 2015 at 6:17
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
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]
Use the following query to obtain the count of records available for each table in descending order (from high to low) - include conditions if necessary:
select distinct t.name as Table_Name,p.rows as Noof_Records from sys.tables t inner join sys.partitions p on t.object_id=p.object_id where create_date\<='15-apr-2025'
order by p.rows desc
