T-SQL Row Count in a Table

In T-SQL usually we use the below query to get the total number of rows in a table

SELECT COUNT(*) FROM tblEmployeeMaster

There is another way to get the total number of rows in a table. But this is more efficient than the above one.

SELECT t.object_id ,t.Name,i.index_id,i.rows FROM sys.tables AS t INNER JOIN sys.partitions AS i
ON t.object_id = i.object_id WHERE i.index_id < 2  AND t.object_id=OBJECT_ID(‘tblEmployeeMaster’)

Of course, you can remove the condition “AND t.object_id=OBJECT_ID(‘tblEmployeeMaster’)” to view all the tables and their row count.

See the performance of the both queries using the Execution Plan

Execution Plan - Row Count
Execution Plan - Row Count

From the above execution plan you can see that Query 1 takes less execution cost than Query 2. So, Query 1 will give good performance than Query 2.

One thought on “T-SQL Row Count in a Table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s