SQL Server Performance

Question on Database Options and NULL

Discussion in 'Getting Started' started by lcerni, Aug 21, 2007.

  1. lcerni New Member

    I just do a straight select.
    QUERY
    Select top 5 * From client_contract_manuf_disc
    OUTPUT
    6 AET001 43 NULL
    6 AET001 262 NULL
    6 AET001 338 NULL
    6 AET001 450 NULL
    6 AET005 43 NULL

    I have a where clause where client_owned_ind is NULL in the table.
    QUERY
    Select top 5 * From client_contract_manuf_disc
    Where client_contract_num <> client_owned_ind
    OUTPUT
    no rows

    I use the ISNULL and rows are returned.
    QUERY
    Select top 5 * From client_contract_manuf_disc
    Where client_contract_num <> isnull(client_owned_ind,0)
    OUTPUT
    6 AET001 43 NULL
    6 AET001 262 NULL
    6 AET001 338 NULL
    6 AET001 450 NULL
    6 AET005 43 NULL
    Simular table but in a different database with a slightly different name.
    QUERY
    Select top 5 * From cc_manuf_disc
    Where client_contract_num <> client_owned_ind
    OUTPUT
    6 AET001 43 39.00 N 10934 2004-10-11 10:37:06.083 0x0000000017AAD194
    6 AET001 262 5.00 N 10441 2007-06-24 12:06:41.023 0x0000000017AAD195
    6 AET001 338 15.00 N 10215 2006-06-30 11:27:55.323 0x0000000017AAD196
    6 AET001 450 100.00 N 10441 2007-06-18 16:49:52.983 0x0000000017AAD197
    6 AET003 43 39.00 N 10934 2004-10-11 10:38:00.927 0x0000000017AAD198

    I took a look at the options for both databases and, for example, "ANSI NULLS Enabled" is False in both.
    Why is it that I can use "Where client_contract_num <> client_owned_ind" in one database and
    get results but in another database I do not but will get results if I use ISNULL.
    What database option is set to allow this? I cannot find it.
    Isn't there an option so that when fillering against a NULL value it still shows the row? I hope that my question is clear.
  2. ranjitjain New Member

    Hi,
    Column value can not be compared with NULL in this way:
    where col1<>NULL, you need to use where col1 IS NOT NULL.
    So in both of your queries you need to use ISNULL if there is any possibility of NULL values.
    You got rows in second query just because columns had non NULL values.
    Insert NULL values in other database and check, you will get blank result without ISNULL.
  3. Adriaan New Member

    You should at all times avoid using functions 'around' columns in a WHERE clause. This is because SQL Server has to evaluate the value returned by the function, and cannot use an appropriate index that might be available - which usually means query performance is worse.
    Try somthing like:
    WHERE client_contract_num <> client_owned_ind OR client_owned_ind IS NULL

Share This Page