When working on tables with a large number of records, one must be careful using a simple statements like SELECT COUNT(*) FROM[Table_Name]; especially when you are bulk inserting items into a table and want to get a count. 

The simple count statement takes forever to return row count on large tables. Its because it uses full table scan to count number of rows. A better approach to get the row count on any table is to query new Dynamic Management Views (DMV) in SQL Server 2005+ sys.dm_db_partition_stats. DMV contains row count and page counts for any table including all the partitions. Please note that if you did not create partitions for your table, your table still going to be created on single default partition; so you are using partitions.

The statement below will provide the total number of rows – very quickly:

SELECT
  Total_Rows= SUM(st.row_count)
FROM
  sys.dm_db_partition_stats st
WHERE
  object_name(object_id) = '!!YOUR TABLE!!'
  and (index_id <2)