Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get Your ANSI_NULLs Settings Consistent Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 8:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
Richard Gibbins (1/27/2010)
The whole point of null is that the value is undefined so comparing a value and saying they are equal if both are null is not valid.

The currently defined behaviour in SQL Server depends on the setting of ANSI_NULLS - see http://msdn.microsoft.com/en-us/library/ms188048.aspx. Thankfully, we are moving to a point where the statement quoted above will be true some day.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #854876
Posted Wednesday, January 27, 2010 10:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:44 PM
Points: 133, Visits: 851
Richard Gibbins (1/27/2010)
I'm probably stirring up a hornet's nest here but I have a problem with the way you are treating nulls. The whole point of null is that the value is undefined so comparing a value and saying they are equal if both are null is not valid. Two columns and/or variables are equal if and only if the values are defined and equal. Code should be aware of nulls and deal with them but not by equating two null values.

Richard


Well, my function is called NullableIntsMatch - not NullableIntsEqual if that helps you see the value in it. The function is super useful; you can inline the code if you don't like the function. But my question to you is how can you find the rows with unassigned values using a stored procedure without comparing null column values to null parameter values?
Post #854907
Posted Thursday, January 28, 2010 12:52 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 8, 2013 3:01 AM
Points: 4, Visits: 453
So why not use something like "ISNULL(IntValue1, -1) = ISNULL(IntValue2, -1)" for every case where IntValueX has to be >-1? I guess this would be faster than calling a function.

Post #854969
Posted Thursday, January 28, 2010 1:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:20 PM
Points: 11,194, Visits: 11,142
xdream (1/28/2010)
So why not use something like "ISNULL(IntValue1, -1) = ISNULL(IntValue2, -1)" for every case where IntValueX has to be >-1? I guess this would be faster than calling a function.

How does that work if -1 is a valid value in the column?

(IntValue1 <> IntValue2) OR (IntValue1 IS NULL AND IntValue2 IS NULL)

...is one correct way to test this type of condition. Just one more reason to avoid NULL values wherever possible.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #854976
Posted Friday, January 29, 2010 2:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:27 AM
Points: 464, Visits: 314
It's important to note that for some settings, like "ANSI_NULL_DEFAULT" and "ANSI_NULLS", the ole db connection overrides the database settings:

"[...] Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server."


___________________________________
I love you but you're standing on my foot.
Post #856415
Posted Monday, February 1, 2010 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 3:25 PM
Points: 5, Visits: 83
And don't forget while you're fixing all the =NULL and <>NULL, you need to also look for this:
CASE <fieldname or expression> WHEN NULL THEN ..... since this is treated as =NULL

Brian
Post #857693
Posted Thursday, November 11, 2010 9:42 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:55 PM
Points: 65, Visits: 352
This article was the key to helping me resolve an issue my team was facing when we upgraded our Linux proxy servers to 64-bit SLES 11, from 32-bit SLES10 SP2. Many of our tools (written in Perl, connecting to SQL Server 2005 server via Linux proxies) were not returning results from our databases, and were not able to execute some stored procedures or make data modifications. No immediate errors were presenting and running the queries directly on our SQL Server 2005 server executed without an issue.

After a lot of debugging and googling, I stumbled across this article and tested it out. This seems to have solved our problems in our test environment. I am planning on deploying my changes to production when are testing has completed though, and I was hoping someone here could tell me whether or not I need to stop our services prior to rolling out. I did not stop services on our test database server, but I also didn't have any tools running while I was making the necessary changes. Is there a problem with making the changes necessary live? We have a 24/7 uptime environment, so it would be a hard sell here for me to require that we go down while I make the changes. However, if I am taking a big risk by making our databases and schema ANSI compliant live, then I will make the case. I just need a little education on the topic. Any resources and/or advice would be greatly appreciated.

Thank you in advance and thank you for this article!

Cathy
Post #1019416
Posted Thursday, November 25, 2010 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:43 PM
Points: 6, Visits: 41
Very helpful article. I now have my databases all set for ANSI compliance. The only issue that I have is that the system stored procedures are not ANSI compliant. Is there any configuration setting to make them ANSI, or do I need to modify the ones I use manually?
eg. [sys].[sp_MSforeachtable] has SET QUOTED_IDENTIFIER OFF

Is there any setting that can change this when creating a new database or working with an existing one, or is it necessary to manually modify all scripts with this setting?

Thanks,
Greg
Post #1026590
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse