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

    Unanswered: SELECT COUNT(*) alternative

    -- SORRY, POSTED THIS IN THE WRONG FORUM, MEANT TO DO IT FOR MSSQL AND IT WOULD NOT LET ME DELETE --

    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
    Last edited by bryanhughes; 11-17-03 at 17:34.

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    If you just do a,

    SELECT count(key_field_name) FROM table_name;

    this will be about as fast as you're going to get because:
    1. the database only has to read key fields instead of
    the entire record, and
    2. the key field is in an index so a full table scan is
    avoided (just the index is used).

    -lv

Posting Permissions

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