Counting the number of rows in a table


Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server.

Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking.

Jes Schultz Borland blogged about it some time ago and also Aaron Bertrand has a blog post on this subject. I will refrain from repeating here what they both said: go read their blogs to understand why COUNT(*) is a not a good tool for this task.

The alternative to COUNT(*) is reading the count from the table metadata, querying sys.partitions, something along these lines:

SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('MyTable')
    AND p.index_id IN (0,1); -- heap or clustered index

Many variations of this query include JOINs to sys.tables, sys.schemas or sys.indexes, which are not strictly necessary in my opinion. However, the shortest version of the count is still quite verbose and error prone.

Fortunately, there’s a shorter version of this query that relies on the system function OBJECTPROPERTYEX:

SELECT OBJECTPROPERTYEX(OBJECT_ID('MyTable'),'cardinality')

Where does it read data from? STATISTICS IO doesn’t return anything for this query, so I had to set up an Extended Events session to capture lock_acquired events and find out the system tables read by this function:

sysalloc

Basically, it’s just sysallocunits and sysrowsets.

It’s nice, short and easy to remember. Enjoy.

Advertisements

Posted on May 18, 2015, in SQL Server, T-SQL and tagged , , , . Bookmark the permalink. 14 Comments.

  1. For stored procedures, I use the method your good article points out. In person, I use COUNT(*) for one and only one reason… it gives me time to take a sip of Scotch. 😉

  2. It looks really useful, but on my DB (SQL Server 2008R2)

    select count(*) from VarOrFormValue
    gives
    23650227

    and
    SELECT OBJECTPROPERTYEX(OBJECT_ID(‘VarOrFormValue’),’cardinality’)
    gives
    NULL

    Clearly I’m missing something obvious????

  3. My bad, I forgot to point out that it only works from 2012 onwards.

  4. In SQL Server 2005 thru 2008, you can do the following to quickly get a row count. Note that prior to 2005, this wasn’t accurate.

    SELECT *
    FROM sys.sysindexes
    WHERE id = OBJECT_ID(‘dbo.YourTableNameHere’)
    AND indid IN (0,1)
    ;

    The “indid” of 0 or 1 will pick up either on the heap or the clustered index depending, of course, on whether there’s a clustered index or not. This could easily be converted to a function to provide similar utility to the 2012 function of OBJECTPROPERTYEX().

    • Thank you. Years of using count(*) and didn’t realise this was an issue – and I thought I knew at least something about SQL!

      SELECT rowcnt
      FROM sys.sysindexes
      WHERE id = OBJECT_ID(‘cpr.VarOrFormValue’)
      AND indid IN (0,1)

      24,444,019 in 75 ms first time, about 25ms thereafter

      select count(*) from cpr.VarOrFormValue

      24,444,019 in 14,569 ms first time, about 750ms thereafter.

      That makes it worthwhile using.

  5. Robert Sterbal

    Do you know at what row count it makes a meaningful difference?

    • Whenever sys.partitions contains a smaller number of rows than the user table. “Meaningful” is highly subjective, but you will see a noticeable difference when the user table is an order of magnitude bigger than sys.partirions.

  6. Martin Thøgersen

    Very easy and fast lookups.
    But I’m hesitant to use it, because we have a table where
    COUNT_BIG(*) >> OBJECTPROPERTYEX(…,’cardinality’)
    How can that be?

  7. @Robert Sterbal,

    If it makes no meaningful difference, then no harm done. It’s one of those things that cause no harm and increases in benefit as the data scales up.

    The only time that you might have to do a significant test to see which is best is if you have something that needs to do a COUNT tens or hundreds times per second.. A COUNT on a short table may be more efficient for such heavy hits.

  8. Using the OBJECTPROPERTYEX method may be a problem from 2014 onwards as it seems to return 0 rows for memory optimized tables e.g. when I run the following against the AdventureWorks2016 database:

    SELECT
    t.[name]
    , t.[object_id]
    , p.[rows] AS [sys.partitions]
    , CONVERT(int,OBJECTPROPERTYEX(t.[object_id],’Cardinality’)) AS [OBJECTPROPERTYEX]
    FROM sys.tables t
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id] AND p.[index_id] IN (0,1)
    WHERE t.[is_memory_optimized] = 1
    ORDER BY t.[name]

    I get the following results:

    name object_id sys.partitions OBJECTPROPERTYEX
    —————————— ———– ——————– —————-
    DemoSalesOrderDetailSeed 1556200594 538 0
    DemoSalesOrderHeaderSeed 1588200708 31465 0
    Product_inmem 2039678314 504 0
    SalesOrderDetail_inmem 468196718 121317 0
    SalesOrderHeader_inmem 212195806 31465 0
    SpecialOffer_inmem 1895677801 16 0
    SpecialOfferProduct_inmem 132195521 538 0

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

%d bloggers like this: