?
Solved

SQLSERVER 2008R2 = NULL not the same as IS NULL

Posted on 2013-12-10
5
Medium Priority
?
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore 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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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