0845 643 64 63

Fast record count in SQL Server using sp_spaceused

Just a quick tidbit today, but a very useful one.
Ever need to do SELECT Count(*) on a very large table? It can take ages.

Instead, use sp_spaceused

EXEC sp_spaceused 'MyTable'

It returns you a useful result set like

name rows reserved data index_size unused
MyTable 19690290 13637488 KB 3001688 KB 9986416 KB 649384 KB

Not only does it also give you extra useful information about the table, but it comes back virtually instantly.

  name – name of the object requested
  rows – the number of rows in the table (or service broker queue)
  reserved – the total reserved space for the object
  data – amount of space used by the data
  index_size – amount of spaced used by indexes in the object
  unused – total space reserved for the object but not yet used

You can also use sp_spaceused without any parameters, and it will return you the stats for the whole database.

Frog-Blog Out

4 comments on “Fast record count in SQL Server using sp_spaceused

  1. Vidas M on said:

    Also you can use very old system view:

    SELECT rowcnt AS NumberOfRows
    FROM sysindexes
    WHERE indid IN (0,1) — heap or cluster
    AND id = OBJECT_ID(‘YourTableName’)

  2. Thanks Vidas, didn’t know about that one

  3. Dattatrey Sindol (Datta) on said:

    The problem with using sp_spaceused is that it does not return the schema name in the output and if you need to get the counts for a list of tables with same name belonging to different schema, then you cannot make out which table count it is.
    Here are few other ways to get the row counts in SQL Server: http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

    • Alex on said:

      Thanks for the link. I tend to use sp_spaceused to get details for a specific schema/table, but yes, if you’re using it to get details on multiple tables then there are better options which include the schema in the result set.

Leave a Reply

Your email address will not be published. Required fields are marked *


371,433 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out