Home Forums SQL Server 2012 SQL Server 2012 - T-SQL SELECT COUNT(*) vs DMVs to get row count RE: SELECT COUNT(*) vs DMVs to get row count

  • April 16, 2015 at 9:57 am

    #1791402

    GilaMonster (4/13/2015)

    Alan.B (4/10/2015)

    Ok, quick update... I did some research on this - it appears that sys.partitions does not guarantee an accurate row count. According to Microsoft, the rows column in sys.partitions "Indicates the approximate number of rows in this partition."

    Because that piece of documentation was copied almost verbatim from sysindexes in SQL 2000, which was prone to errors and inaccuracies. It was trivial in SQL 2000 to find cases where sysindexes's row count did not match Count(*) from table, in SQL 2005+, that should not happen with sys.partitions, if it does, it is a bug (from one of the dev team)

    Thanks for your comments and sorry for the late reply - I'm buried and behind on a project....

    I did notice that that documentation has not changed through 2014 in BOL.

    So I'm clear, you're saying that if COUNT(*) from a table and rows sys.partitions don't match it would be because of a bug? This per the someone on the MS SQL Server Dev Team?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001