SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY row_count DESC
The elapsed time for the above query on the Adventure Works database was only 3ms!! While if we use the COUNT function on the largest table in the database
SELECT COUNT(*) FROM Sales.SalesOrderDetail
The above query took 12ms to get the count for just one table. Obviously, the biggest limitation on using the former approach is that you can't filter data using a where clause but if you need to count all the records, i suggest trying the first query :)
For more details, check this detailed blog entry
Nice Article !
ReplyDeleteReally this will help to people of SQL Server Community.
I have also prepared small note on this, find the total row count for all table of SQL Server.
http://www.dbrnd.com/2015/11/sql-server-script-to-find-total-row-count-of-all-tables-in-a-database/
Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from .Net Core Training in Chennai. or learn thru .Net Core Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
ReplyDeleteor Es6 Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.