SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by William Talada / Get Your ANSI_NULLs Settings Consistent / Latest Posts

RE: Get Your ANSI_NULLs Settings Consistent

_greg — Thu, 25 Nov 2010 16:27:17 GMT

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 OFFIs 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

RE: Get Your ANSI_NULLs Settings Consistent

Cathy DePaolo — Thu, 11 Nov 2010 09:42:59 GMT

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

RE: Get Your ANSI_NULLs Settings Consistent

Brian Scarboro — Mon, 01 Feb 2010 20:45:34 GMT

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 =NULLBrian

RE: Get Your ANSI_NULLs Settings Consistent

AlreadyPicked — Fri, 29 Jan 2010 14:17:17 GMT

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."

RE: Get Your ANSI_NULLs Settings Consistent

Paul White — Thu, 28 Jan 2010 01:14:20 GMT

[quote][b]xdream (1/28/2010)[/b][hr]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.[/quote]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.

RE: Get Your ANSI_NULLs Settings Consistent

xdream — Thu, 28 Jan 2010 00:52:38 GMT

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.

RE: Get Your ANSI_NULLs Settings Consistent

Bill Talada — Wed, 27 Jan 2010 22:18:40 GMT

[quote][b]Richard Gibbins (1/27/2010)[/b][hr]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[/quote]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?

RE: Get Your ANSI_NULLs Settings Consistent

Paul White — Wed, 27 Jan 2010 20:26:59 GMT

[quote][b]Richard Gibbins (1/27/2010)[/b][hr]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.[/quote]The currently defined behaviour in SQL Server depends on the setting of ANSI_NULLS - see [url]http://msdn.microsoft.com/en-us/library/ms188048.aspx[/url]. Thankfully, we are moving to a point where the statement quoted above will be true some day.

RE: Get Your ANSI_NULLs Settings Consistent

James Stephens — Wed, 27 Jan 2010 11:37:44 GMT

I have been bitten by the IN/NOT IN null before...it's a tough one that had me scratching my head for an hour or so. That would make a good qod.--Jim

RE: Get Your ANSI_NULLs Settings Consistent

Richard Gibbins — Wed, 27 Jan 2010 09:13:57 GMT

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

RE: Get Your ANSI_NULLs Settings Consistent

Bill Talada — Wed, 27 Jan 2010 02:53:26 GMT

[quote][b]andy.gray (1/27/2010)[/b][hr]Most interesting but I have a little query.Why is it necessary to have a function for comparing nullable ints but not for any other data types? Is there something specific about nullable ints?Kind regardsAndy[/quote]The need for nullable compares is for all data types. I only have three in place for int, varchar, and datetime types so far for my needs. With a simple find/replace you can generate the others.

RE: Get Your ANSI_NULLs Settings Consistent

andy.gray — Wed, 27 Jan 2010 02:19:00 GMT

Most interesting but I have a little query.Why is it necessary to have a function for comparing nullable ints but not for any other data types? Is there something specific about nullable ints?Kind regardsAndy

RE: Get Your ANSI_NULLs Settings Consistent

Bill Talada — Wed, 27 Jan 2010 00:57:36 GMT

I'm happy people are finding some value in the article; I had sent in a rough draft to see if it would be a topic of interest but they published it without asking me to tighten it up. My main purpose for writing the article was to show people how to be able to rip out all ANSI type statements from all their scripts and to never have to think again about any odd behavior of nulls on a per script basis.

RE: Get Your ANSI_NULLs Settings Consistent

xdream — Wed, 27 Jan 2010 00:05:14 GMT

Nice article, just followed your steps on our databases. :-DThough for general purpose, I'm missing some short explanations on ANSI settings and as you check them in step 2) and it's not only about ANSI NULL.In step 4) I first was kind of surprised as the sql-statement will find other "= NULL"s, too (e.g. SET @Var = NULL). But it shows a starting point for checking your code automatically and fast.Best regards

RE: Get Your ANSI_NULLs Settings Consistent

Paul White — Tue, 26 Jan 2010 22:21:42 GMT

[quote][b]CirquedeSQLeil (1/26/2010)[/b][hr]Good article. I couldn't add anything more than what Paul has already indicated. Maybe a follow-up article on the same subject to go along with that QOD?;-)[/quote]Good idea...

RE: Get Your ANSI_NULLs Settings Consistent

SQLRNNR — Tue, 26 Jan 2010 22:16:21 GMT

Good article. I couldn't add anything more than what Paul has already indicated. Maybe a follow-up article on the same subject to go along with that QOD?;-)

RE: Get Your ANSI_NULLs Settings Consistent

Paul White — Tue, 26 Jan 2010 22:06:44 GMT

An interesting article on an important subject. I would have rated it 5 stars instead of 4 if it had gone further in explaining what might need to be done to fix potential problems - sadly it is not as simple as replacing = NULL with IS NULL.ANSI_NULLS OFF behaviour is decidedly odd, and often counter-intuitive, no wonder Microsoft are keen to stop supporting it. I have an example of this type of weirdness pending as a Question of the Day (QotD) so I'll just say that one example of weirdness involves IN and NOT IN...

Get Your ANSI_NULLs Settings Consistent

Bill Talada — Tue, 26 Jan 2010 22:03:07 GMT

Comments posted to this topic are about the item [B]Get Your ANSI_NULLs Settings Consistent[/B]