Professional Web Applications Themes

Is Null returning incosistent results -- help! - Microsoft SQL / MS SQL Server

We have a very large database of customers and we are trying to do a count on whether a specific field in the customer table is null or not null. The numbers are changed for privacies sake but here is what's happening. SELECT count(*) FROM maillist result: ~4000 ---- SELECT count(*) FROM maillist WHERE email is NOT NULL result: ~2300 ---- SELECT count(*) FROM maillist WHERE email IS NULL result: ~3950 It seems to me that these should be mutually exclusive. Am I missing something? Jason...

  1. #1

    Default Is Null returning incosistent results -- help!

    We have a very large database of customers and we are trying to do a
    count on whether a specific field in the customer table is null or not
    null.

    The numbers are changed for privacies sake but here is what's
    happening.

    SELECT count(*)
    FROM maillist

    result: ~4000

    ----

    SELECT count(*)
    FROM maillist
    WHERE email is NOT NULL

    result: ~2300

    ----

    SELECT count(*)
    FROM maillist
    WHERE email IS NULL

    result: ~3950


    It seems to me that these should be mutually exclusive. Am I missing
    something?

    Jason
    Jason Guest

  2. #2

    Default Re: Is Null returning incosistent results -- help!

    4000 rows is far from a large database (or at least that table is pretty
    small). In fact, you can even do a visual inspection of all the rows if you
    wish.

    Make sure you keep your indexes "fresh" (have a scheduled job for DBCC
    REINDEX or DBCC INDEXDEFRAG). Also, run DBCC CHECKDB periodically to detect
    any problems.

    What version and what Service Pack level are you running?

    HTH,
    Tore.


    "Jason Sirota" <com> wrote in message
    news:google.com... 


    Tore Guest

  3. #3

    Default Re: Is Null returning incosistent results -- help!

    Jason,

    What do you get when you run the following (which will not use indexes):

    select
    count(case when email is null then 1 end) as NullCount,
    count(case when email is not null then 1 end) as NotNullCount,
    count(case when email is not null then 1 else 1 end) as BothCount,
    count(*)
    from maillist

    ?

    Also, is maillist a table or a view?

    -- Steve Kass
    -- Drew University
    -- Ref: ACC17FE8-61C5-48A1-AB86-1D3D99020E86

    Jason Sirota wrote:
     

    Steve Guest

  4. #4

    Default Re: Is Null returning incosistent results -- help!

    it turned out to be a problem with the service pack. Thank you
    everyone for your help.

    Jason

    "jobi" <group> wrote in message news:<bgd1lt$630$wxs.nl>... [/ref]
    Jason Guest

Similar Threads

  1. weird results: (null!=null)??
    By alex in forum MySQL
    Replies: 7
    Last Post: September 28th, 07:33 AM
  2. #25503 [Bgs]: SQLite_query() returning NULL, not FALSE
    By o_gangrel at hotmail dot com in forum PHP Development
    Replies: 1
    Last Post: September 12th, 12:46 AM
  3. OnEditCommand - .FindControl Returning Null
    By George Durzi in forum ASP.NET General
    Replies: 0
    Last Post: July 14th, 09:54 PM
  4. OnEditCommand - FindControl returning Null
    By George Durzi in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 14th, 09:37 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