Fastest way to get the Row Count of a table

Finding the row count of a table is probably the most frequently performed task by SQL developers/programmers.  So the obvious thing we all look for is: what is the fastest way to find the row count of a table?  Of course there are few ways, I’ll discuss them in this post.  However, if you ask me the same question my immediate response without blinking an eye will be using built-in stored procedure SP_SPACEUSED.  It is very easy to use with simple syntax:
--------------------------------------------------------------------------------------------------------
sp_spaceused your_table_name
--------------------------------------------------------------------------------------------------------
and within a second you will get the rowcount of the given table, doesn't matter how big the table is. To know more about the SP_SPACEUSED please see: sp_spaceused (Transact-SQL)

Remarks:

Now there are certain things you need to keep in mind while using sp_spaceused:
1. sp_spaceused relies on DMV sys.dm_db_partition_stats to get the rowcount.  So there is a chance that you will get the approximate row count if statistics are not updated.  To get the exact count you can update the statistics using DBCC UPDATEUSAGE .  However this can take long time if the table is really huge. 

2. If the table is part of the default dbo schema then you don’t need to pass the schema name in the syntax.  However if the table is created under some different or custom schema then you need to pass the schema name also.  But if you try to execute sp_spaceused schema_name.your_table_name you will get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

This become even more difficult if schema name has your domain name as well, e.g. your table name is domain\user_name.table_name and you try to use sp_spaceused domain\user_name.table_name you will get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

In that case you just need to pass your table name within the single quotes:
--------------------------------------------------------------------------------------------------------
sp_spaceused 'schema_name.your_table_name'
go
-- or
sp_spaceused 'domain\schema_name.your_table_name'
--------------------------------------------------------------------------------------------------------

Now coming to back some positives:  This is my favorite method despite the fact that sp_spaceused sometimes can give me approximate row count.   First reason I like sp_spaceused is, it is extremely fast, syntax is simple, I don’t need to remember some DMV names.  Another reason is, I can use it even when some DML operations are being performed on the table. For example you are inserting large number of rows in a table, query is still executing and you want to know how many rows are inserted in the table.  sp_spaceused can you give you the row count of a table even middle of a INSERT query.  Very very useful in tracking the progress of big INSERT query. 

Now having said that row count we get from sp_spaceused or the DMV sys.dm_db_partition_stats is not always accurate, personally I have never observed any difference.   Even if there is a difference I guess it is negligible. 

Now quickly discuss the couple of other methods to find the row count of a table.

Using DMV sys.dm_db_partition_stats:

This is another very quick method to get the rowcount of a table.
--------------------------------------------------------------------------------------------------------
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('table_name')
AND index_id < 2;
--------------------------------------------------------------------------------------------------------
Interesting thing is even sp_spaceused relies on the same table to get the row_count.  But of course writing and remembering sp_spaceused table_name is much easier than this, right?  You can also get the row count using sysindexes, but I’m skipping that.

Using COUNT function:

Now this is the most reliable, known but also a slow method to find the row count of a table, especially if the table size  is in millions or billions.   Actually I don’t need to talk about the COUNT function, because everyone knows about it, but I’m mentioning it in this post because of a myth.  There is some misconception going around that if you write COUNT(1) instead of COUNT ( * ) you will get faster result.  That is a total myth.  Many experts have tested it, written about it and you can find many articles online comparing the performance of COUNT(1) and COUNT ( * )  and concluding that there is absolutely no performance difference between them.   Here is one such article by Beaulin Twinkle: SQL Server: count(*) or count (1) or count(”) Which is better?

Thanks
Mangal Pardeshi