Find Number of Rows in a Table in SQL Server

Getting a count from a table is something very simple on SQL Server. When I was a beginner in SQL Server, my only intent of getting the count from a table was with function, count (*). But, I never had a realization that there were other methods too for fetching count and as well at a faster pace.

Let’s look at different ways to find the number of rows in a table

1. Count(*)

Count(*) is the most commonly used method to get the records to count from a table. The Count(*) can be used when the table count is low. But, if the table is big, then the IO STATISTICS shows that the SQL does a lot of work at the backend to get the row count as a result set.

Let’s consider SQLArena_TEST as our table to check the count.

Example: SELECT COUNT(*) FROM SQLArena_TEST

2. Count(1)

Count(1) cannot be considered the perfect one to get the count in terms of performance. But while considering the IO STATISTICS, the count(1) does a better job than the count(*).

Example: SELECT COUNT(1) FROM SQLArena_TEST

3. Join between sys.tables, sys.indexes, and sys.partitions

The query below can give us an incredibly fast row count retrieval that joins between sys.tables, sys.indexes, and sys.partions. As the join happens between the table properties, indexes check, and the storage, there is always approximation at this level of fetching counts.

SELECT CAST(a.rows AS float)

FROM sys.tables AS tab

INNER JOIN sys.indexes AS ind ON ind.object_id = tab.object_id and ind.index_id < 2

INNER JOIN sys.partitions AS a ON a.object_id=CAST(tab.object_id AS int)

AND a.index_id=ind.index_id

WHERE ((tab.name=N'SQLArena_TEST' AND SCHEMA_NAME(tab.schema_id)='dbo'))

4. Using sys.dm_db_partition_stats

Using dynamic management views, sys.dm_db_partition_stats, the counts can be achieved. But they can sometimes give incorrect record count if your catalog views are incorrect at a page level. This can be corrected by using the consistency check. However, let’s not get deviated on the fix that we have for inaccuracy in counts using the DMV. Here is the script for count using DMV.

SELECT SUM (row_count)

FROM sys.dm_db_partition_stats

WHERE object_id=OBJECT_ID('SQLArena_TEST')

AND (index_id=0 or index_id=1);