Post reply

Can you count columns from sys.partition table, if not is select count(1) the fastest way ?

  • May 6, 2014 at 12:42 pm

    #288531

    Can you count columns from a variation of the following query ?

    set statistics time on

    SELECT

    OBJECT_SCHEMA_NAME(object_id) as dbo

    ,OBJECT_NAME(object_id) as rows

    ,SUM(Rows) AS NumOfRows --sum the rows if there are multiple partitions

    FROM

    sys.partitions

    WHERE

    index_id < 2 --ignore the partitions from non-clustered indexes if any

    and OBJECT_NAME(object_id) = 'table'

    GROUP BY

    OBJECT_ID

    ORDER BY

    NumOfRows DESC

    If not, is the next query the fastest way?

    set statistics time on

    SELECT COUNT(1) cnt, company FROM table

    group by company

    order by cnt desc

    Thanks

  • May 6, 2014 at 1:03 pm

    #1711231

    Yes, you can use sys.partitions if you want unfiltered row count. If you want to query the table directly, then SELECT COUNT(*) ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply