Professional Web Applications Themes

COUNTING PROBLEM !!! - Microsoft SQL / MS SQL Server

I have a serious problem: Each time I run the following query the count result is changing! SELECT count(*) from posnad WHERE lfn_pos IS NULL lfn_pos is defined as an 'int' and used as a foreign key. The table is quite big (8,000,000 rows) and the count result should return about 1,600,000 rows. There are NO updates, insertions, deletions, triggers or what ever on the table. I am the only user. Transaction isolation level is READ COMMITED and a already tried the SET NOCOUNT option. Any ideas??? Regards, ------------ Olaf Bönning IT Specialist...

  1. #1

    Default COUNTING PROBLEM !!!

    I have a serious problem: Each time I run the following
    query the count result is changing!

    SELECT count(*) from posnad WHERE lfn_pos IS NULL

    lfn_pos is defined as an 'int' and used as a foreign key.
    The table is quite big (8,000,000 rows) and the count
    result should return about 1,600,000 rows.

    There are NO updates, insertions, deletions, triggers or
    what ever on the table.
    I am the only user. Transaction isolation level is READ
    COMMITED and a already tried the SET NOCOUNT option.

    Any ideas???

    Regards,

    ------------
    Olaf Bönning
    IT Specialist
    Olaf Bönning Guest

  2. #2

    Default Re: COUNTING PROBLEM !!!

    Olaf, you'd be surprised how many times people were sure that nothing modified their tables and eventually found out they were wrong.
    Try running a Profiler trace to monitor modifications against the table (you can set a filter on objectid).

    --
    BG, SQL Server MVP
    Solid Quality Learning
    [url]www.solidqualitylearning.com[/url]


    "Olaf Bönning" <Olaf.Brechtgmx.net> wrote in message news:050201c3444c$ba68cee0$a301280aphx.gbl...
    I have a serious problem: Each time I run the following
    query the count result is changing!

    SELECT count(*) from posnad WHERE lfn_pos IS NULL

    lfn_pos is defined as an 'int' and used as a foreign key.
    The table is quite big (8,000,000 rows) and the count
    result should return about 1,600,000 rows.

    There are NO updates, insertions, deletions, triggers or
    what ever on the table.
    I am the only user. Transaction isolation level is READ
    COMMITED and a already tried the SET NOCOUNT option.

    Any ideas???

    Regards,

    ------------
    Olaf Bönning
    IT Specialist
    Itzik Ben-Gan Guest

  3. #3

    Default Re: COUNTING PROBLEM !!!

    Olaf,
    The set nocount will not effect the count in your query
    Try this
    SELECT lfn_pos, count(*) as count from posnad group by lfn_pos
    this will count per grouping of lfn_pos
    Is posnad a table or query?

    What version of SQL are you using?

    Basicall if there really are no changes to the table then the fighures
    should remain the same, I don't remember reading about any bugs like this in
    the latest SP (I asume your on the latest SP?)

    If posnad is a view then mayb the view details are changing?


    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url]



    "Olaf Bönning" <Olaf.Brechtgmx.net> wrote in message
    news:050201c3444c$ba68cee0$a301280aphx.gbl...
    I have a serious problem: Each time I run the following
    query the count result is changing!

    SELECT count(*) from posnad WHERE lfn_pos IS NULL

    lfn_pos is defined as an 'int' and used as a foreign key.
    The table is quite big (8,000,000 rows) and the count
    result should return about 1,600,000 rows.

    There are NO updates, insertions, deletions, triggers or
    what ever on the table.
    I am the only user. Transaction isolation level is READ
    COMMITED and a already tried the SET NOCOUNT option.

    Any ideas???

    Regards,

    ------------
    Olaf Bönning
    IT Specialist


    Greg Obleshchuk Guest

  4. #4

    Default COUNTING PROBLEM !!!

    I started the execution plan in Query yzer and found
    out that the numbers are calculated using an index scan
    and not a table scan. Could my index be corrupt?

    ------------
    Olaf Bönning
    IT Specialist

    Olaf Bönning Guest

Similar Threads

  1. counting down in a for
    By drowl@23.me.uk in forum PERL Beginners
    Replies: 4
    Last Post: December 12th, 04:14 PM
  2. Counting (easy!)
    By Trent Rigsbee in forum PERL Beginners
    Replies: 24
    Last Post: November 15th, 03:29 AM
  3. Counting (easy!) (YES!!)
    By Trent Rigsbee in forum PERL Beginners
    Replies: 2
    Last Post: November 13th, 02:35 AM
  4. #23132 [Csd]: Strange engine crash (reference counting problem)
    By moriyoshi@php.net in forum PHP Development
    Replies: 0
    Last Post: October 13th, 11:25 PM
  5. Counting files
    By Shawn Corey in forum PERL Miscellaneous
    Replies: 12
    Last Post: September 11th, 12:23 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139