Post reply

Count(*) giving wrong Value on SQL 2000

  • July 31, 2008 at 12:38 pm

    #72495

    Count(*) giving me wrong Value.

    select count(*) from Table_Name gives me 55,000,000

    select rows from DB_NAME..sysindexes

    where indid < 2

    and id = object_id('Table_Name')

    I get 56,000,000

    When I do >> select * INTO NEW_TABLE from Table_Name, The count in NEW_TABLE is 56,000,000

    Can anyone tell me what could be the reason

    Thanks.

  • July 31, 2008 at 12:53 pm

    #851489

    Things could be out of sync here. An index rebuild/statistics update should fix things.

  • July 31, 2008 at 1:07 pm

    #851496

    COUNT(*) is exact and correct. SysIndexes is approximate and sometimes incorrect.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 31, 2008 at 1:16 pm

    #851499

    Is there a way to find if things are out of sync and also why are the out of sync.

    Thanks,

    Jay

  • July 31, 2008 at 1:17 pm

    #851500

    rbarryyoung (7/31/2008)

    COUNT(*) is exact and correct. SysIndexes is approximate and sometimes incorrect.

    When I do >> select * INTO NEW_TABLE from Table_Name, The count in NEW_TABLE is 56,000,000

    So, count(*) is Incorrect on the older table as the count(*) matches to value in Sysindex after Select INTO.

  • July 31, 2008 at 2:02 pm

    #851527

    Now I ran a Query

    Select count(*) from Table_name where column_1 is not null.

    Column_1 is a NON Null column on the Table. I get count of 56,000,000 which mirrors value in Sysindexes and it is 1 Million more than when I run >> Select count(*) from Table_name (55,000,000)

    Can someone give me any pointers.

    Thanks.

  • July 31, 2008 at 2:05 pm

    #851530

    Sounds like a bug in SQL Server to me then, unless there is more to these commands than we are being shown.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 31, 2008 at 2:06 pm

    #851532

    That's what it seems like to me. I am running above mentioned queries.

  • July 31, 2008 at 2:09 pm

    #851534

    You can certainly get this behavior with COUNT(ColName), but it's not supposed to happen with COUNT(*).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 31, 2008 at 2:21 pm

    #851538

    Yeah I know as COUNT(ColName) eliminates Null values.

    Any pointers?

    -Jay

  • July 31, 2008 at 4:01 pm

    #851573

    When I do >> select * INTO NEW_TABLE from Table_Name, The count in NEW_TABLE is 56,000,000

    How do you know that?

    By running select COUNT(*) FROM NEW_TABLE ?

    If so then COUNT(*) works and there is no bug.

    It's just your table got corrupted.

    Drop it and rename NEW_TABLE to Table_Name.

    _____________
    Code for TallyGenerator

Viewing 11 posts - 1 through 10 (of 10 total)

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