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.

Columns:
  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 Responses to Fast record count in SQL Server using sp_spaceused

  • 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’)

  • Thanks Vidas, didn’t know about that one

  • 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/

    • 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 *

HTML tags are not allowed.

344,224 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon