Solved

SQLSERVER 2008R2 = NULL not the same as IS NULL

Posted on 2013-12-10
5
425 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

10 Experts available now in Live!

Get 1:1 Help Now