What are Different Methods to Know the Row Count of Table? – Interview Question of the Week #261

Question: What are Different Methods to Know the Row Count of Table?

Answer: The answer to this question is very simple and many. I have previously blogged about this topic many times. Here are a few sample questions one can on this topic:

• How many users created tables are there in the database?
• How many non clustered indexes each of the tables in the database have?
• Is table Heap or has clustered index on it?
• How many rows each of the tables is contained in the database?

Let us see the answers.

Different Methods

Method 1: Using sys.partitions

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

Source: SQL SERVER – How to Find Row Count of Every Table in Database Efficiently?

Method 2: Using dm_db_partition_stats

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO

Source: SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

Method 3: Using SSMS

Source: SQL SERVER – Find Table Row Count Without Using T-SQL and Without Opening Table

Method 4: Using sys.indexes

SELECT [schema_name] = s.name, table_name = o.name,
MAX(i1.type_desc) ClusteredIndexorHeap,
MAX(COALESCE(i2.NCIC,0)) NoOfNonClusteredIndex,
p.rows
FROM sys.indexes i
RIGHT JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID AND p.index_id IN (0,1)
LEFT JOIN sys.indexes i1 ON i.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1)
LEFT JOIN (SELECT object_id,COUNT(Index_id) NCIC
FROM sys.indexes
WHERE type = 2
GROUP BY object_id) I2
ON i.OBJECT_ID = i2.OBJECT_ID
WHERE o.TYPE IN ('U')
GROUP BY s.name, o.name, p.rows
ORDER BY schema_name, table_name

Source: SQL SERVER – Find Rows and Index Count – SQL in Sixty Seconds #029 – Video

Method 5: Using Count(*)

This has to be my least favorite method.

SELECT COUNT(*)
FROM TableName

Source: SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

Well, these are just a few tricks to know how we can find out the row count of a table with SQL Server.

Additionally, there are a few other blog posts which you may find interesting. SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*). Here is the same puzzle for you and I would like to see what your answer to this question. Question: SELECT * gives error when executed alone, but SELECT COUNT (*) does not. Why? There may be many different answers to this question. I strongly suggest you read the original blog post.

Please post your views in the comments section and if you use any other method, do leave a note and I will post a blog post with due credit to you. You can reach out to me via LinkedIn here.

Reference: Pinal Dave (https://blog.sqlauthority.com)