Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Your ANSI_NULLs Settings Consistent


Get Your ANSI_NULLs Settings Consistent

Author
Message
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1817
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?
xdream
xdream
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
AlreadyPicked
AlreadyPicked
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 329
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.
Brian Scarboro
Brian Scarboro
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
dbDiva
dbDiva
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 359
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
_greg
_greg
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 48
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search