SQL Server Performance

need explaining - query nature

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by adyseven, Jul 28, 2008.

  1. adyseven New Member

    Dear All,

    I have a little question for u guys...please help because i'm newbie here.

    In our database, we have nullable field that categorize customer, for example the value for this field are A,B,C, and D.

    In some case, i need to query for searching customer whose category is not "D". This mean i need to get customer
    whose category are A,B,C and NULL

    this is my first query:

    select cust_id, cust_name, cust_catg from customer where cust_catg <> 'D'

    anyway the result is all customer whose category are A,B,C, but the NULLs is not retrieved (however, I need NULL Categorize customer too)

    so i change the query with the second one, then the result is fit with i expected

    select cust_id, cust_name, cust_catg from customer where (cust_catg <> 'D') or (cust_catg is NULL)

    Q:

    is there any different in Query nature between SQL 2005 and 2000? because i remember that in sql server 2000 the first query is enough
    to retrieve the result i wanted, but it is different in sql 2005, please CMIIW...
  2. Adriaan New Member

    In SQL 2000 and 2005, the behaviour should be the same.
    However, there are some session/connection settings that will affect the behaviour around NULLs in comparisons - check the entry for SET ANSI_NULLS statement in Books Online, and so on.
    Actually, the behaviour that you saw in SQL 2000 is non-standard.
  3. adyseven New Member

    Hi Adriaan,
    Thx for the reply...this is enough to answer my question...

Share This Page