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.

About these ads

One response to this post.

  1. [...] has a better performance than the “count(*)” query. If you want to know why look at http://asganesh.wordpress.com/2009/08/29/t-sql-row-count-in-a-table/ Share this:TwitterFacebookLike this:LikeBe the first to like this post. This entry was posted in [...]

    Reply

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: