Solved

SQLSERVER 2008R2 = NULL not the same as IS NULL

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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