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.
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;
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 &lt; 2 ORDER BY st.row_count DESC GO
Method 3: Using SSMS
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
Method 5: Using Count(*)
This has to be my least favorite method.
SELECT COUNT(*) FROM TableName
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)