Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18

    Unanswered: SELECT COUNT(*) alternative question...

    I have read in a few TSQL Performance postings that you can use:

    SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tablename') AND indid < 2

    versus

    SELECT COUNT(*) FROM tablename

    Unfortunately, I am trying this with our database and am consistantly getting a different count, even across different tables.

    I tried tables with clustered indexes vs. non-clustered indexes and no difference. The SELECT COUNT(*) always yeilds a handful more records.

    I also excluded the possibility that the table was growing between query executions.

    Anyone have any insight to this trick and my discrepency?

    Thanks,
    Bryan Hughes

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: SELECT COUNT(*) alternative question...

    Originally posted by bryanhughes
    I have read in a few TSQL Performance postings that you can use:

    SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tablename') AND indid < 2

    versus

    SELECT COUNT(*) FROM tablename

    Unfortunately, I am trying this with our database and am consistantly getting a different count, even across different tables.

    I tried tables with clustered indexes vs. non-clustered indexes and no difference. The SELECT COUNT(*) always yeilds a handful more records.

    I also excluded the possibility that the table was growing between query executions.

    Anyone have any insight to this trick and my discrepency?

    Thanks,
    Bryan Hughes
    DBCC Updateusage will solve your problem. Sysindexes tables is not constantly updated and might contain inaccuracies. Quoting from the holy book

    DBCC UPDATEUSAGE
    Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

    Syntax
    DBCC UPDATEUSAGE
    ( { 'database_name' | 0 }
    [ , { 'table_name' | 'view_name' }
    [ , { index_id | 'index_name' } ] ]
    )
    [ WITH [ COUNT_ROWS ] [ , NO_INFOMSGS ]
    ]

    Arguments
    'database_name' | 0

    Is the name of the database for which to report and correct space usage statistics. Database names must conform to the rules for identifiers. For more information, see Using Identifiers. If 0 is specified, then the current database is used.

    'table_name' | 'view_name'

    Is the name of the table or indexed view for which to report and correct space usage statistics. Table and view names must conform to the rules for identifiers.

    index_id | 'index_name'

    Is the identification (ID) number or index name of the index to use. If not specified, the statement processes all indexes for the specified table or view.

    COUNT_ROWS

    Specifies that the rows column of sysindexes is updated with the current count of the number of rows in the table or view. This applies only to sysindexes rows that have an indid of 0 or 1. This option can affect performance on large tables and indexed views.

    NO_INFOMSGS

    Suppresses all informational messages.

    Remarks
    DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    Thanks! My next question is then if I have to do DBCC UPDATEUSAGE each time, this trick really isnt a very good trick?

    Or once I do the UPDATEUSAGE, it will stay accurate for a while?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is not a good idea to use this trick if you require an accurate rowcount, for the reasons Enigma outlined.

    SQL Server can perform a select count(*) extremely quickly, so just go ahead and use that instead.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •