Solved

SQLSERVER 2008R2 = NULL not the same as IS NULL

Posted on 2013-12-10
5
424 Views
Last Modified: 2013-12-10
Is there a difference between = NULL and IS NULL in sqlserver 2008R2

Example: If  I change all the “ = NULL ” in this statement:

SELECT SUM ( ACT_MATERIAL_COST ), SUM ( ACT_LABOR_COST ), SUM ( ACT_BURDEN_COST ), SUM ( ACT_SERVICE_COST ), SUM ( QTY ) FROM INVENTORY_TRANS WHERE TYPE = 'O' AND WORKORDER_BASE_ID = NULL AND WORKORDER_LOT_ID = NULL AND WORKORDER_SPLIT_ID = NULL AND WORKORDER_SUB_ID = NULL AND CUST_ORDER_ID = 'CC130713' AND CUST_ORDER_LINE_NO = 7.00000000 AND TRANSACTION_ID <= 415924
Result:  NULL NULL NULL NULL NULL

To “ IS NULL ” I get the correct results:

SELECT SUM ( ACT_MATERIAL_COST ), SUM ( ACT_LABOR_COST ), SUM ( ACT_BURDEN_COST ), SUM ( ACT_SERVICE_COST ), SUM ( QTY ) FROM INVENTORY_TRANS WHERE TYPE = 'O' AND WORKORDER_BASE_ID IS NULL AND WORKORDER_LOT_ID IS NULL AND WORKORDER_SPLIT_ID IS NULL AND WORKORDER_SUB_ID IS NULL AND CUST_ORDER_ID = 'CC130713' AND CUST_ORDER_LINE_NO = 7.00000000 AND TRANSACTION_ID <= 415924
Result: 0.99 0.00 0.00 0.00 1.0000
0
Comment
Question by:BFanguy
  • 3
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 39708825
Yes, there is a difference.  Now ... there is a SQL Server setting that can be changed for that, iirc, but in standard SQL (and T-SQL which is the MS variant), you use IS with NULL, not =.

= really compares values.  And NULL isn't a value ... it's undefined.  So strictly speaking, nothing can ever = NULL.  Not even NULL = NULL!
0
 

Author Comment

by:BFanguy
ID: 39708846
Thanks, do you happen to have the setting in SQL server that can be changed?
0
 

Author Comment

by:BFanguy
ID: 39708849
This statement is running from a purchased software product.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39708870
SET ANSI_NULLS OFF

http://technet.microsoft.com/en-us/library/ms188048.aspx

The setting is deprecated and will go away eventually ... but is supported through SQL 2012.
0
 

Author Closing Comment

by:BFanguy
ID: 39708877
Thank you very much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now