How to Get Table Row Count in SQL Server

by

Since I used to get the requirement to determine the size of our data, it is crucial to know the row count in an SQL server table. As a senior developer in SQL, I have identified a few simple approaches to do this. In this article, I will walk you through all those approaches individually.

Approach-1: Using Select COUNT(*) / SELECT COUNT(1)

The basic approach for this purpose is to use the Select COUNT(*) statement.

Syntax

SELECT COUNT(*) FROM tablename

Example

In this example, we will get the record count of the Vehicle table. 

SELECT COUNT(*) FROM Vehicle

After executing the above query, I got the expected output, as shown in the screenshot below.

How to Get Table Row Count in SQL Server

You can also use COUNT(1) instead of COUNT(*), as shown below.

SELECT COUNT(1) FROM Vehicle

After executing the above query, I got the expected output below.

how to check table row count in sql server

Check out How To Get Table Count In SQL Server

Approach-2: Using sp_spaceused Stored Procedure

We can execute the sp_spaceused stored procedure to get the row count, including all other information.

Syntax

EXEC sp_spaceused 'TableName'

Example

We can execute the query below to get the row count, including all other information.

EXEC sp_spaceused 'Vehicle'

After executing the able query, I got the expected output, as shown in the screenshot below.

select table row count sql server

Check out How To Check Table Description In SQL Server

Approach-2: Using sys.dm_db_partition_stats

We can also use the sys.dm_db_partition_stats for this purpose.

Syntax

SELECT SUM(row_count) AS TableRowCount
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TableName')
    AND index_id < 2

Example

We can execute the query below to get the row count for the Vehicle table.

SELECT SUM(row_count) AS TableRowCount
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Vehicle')
    AND index_id < 2

After executing the above query, I got the expected output, as shown in the screenshot below.

Get table row count in sql server

Conclusion

Getting the table row count is easy using Select COUNT(*) / SELECT COUNT(1). Based on your requirements, you can also utilize any other approaches mentioned in this article.

You may also like following the articles below.