Get Your ANSI_NULLs Settings Consistent

Question

Get Your ANSI_NULLs Settings Consistent

Bill Talada

SSChampion

Points: 11954
More actions
January 26, 2010 at 10:03 pm

#209471

Comments posted to this topic are about the item Get Your ANSI_NULLs Settings Consistent

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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?;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 3

CirquedeSQLeil (1/26/2010)
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?;-)

Good idea...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

xdream Valued Member Points: 58 More actions · Answer 4

Nice article, just followed your steps on our databases. 😀

Though 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

Bill Talada SSChampion Points: 11954 More actions · Answer 5

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.

andy.gray SSC Journeyman Points: 98 More actions · Answer 6

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 regards

Andy

Bill Talada SSChampion Points: 11954 More actions · Answer 7

andy.gray (1/27/2010)
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 regards
Andy

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.

Richard Gibbins Old Hand Points: 377 More actions · Answer 8

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

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 9

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

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
SQLkiwi blog
@SQL_Kiwi

Bill Talada SSChampion Points: 11954 More actions · Answer 11

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 Valued Member Points: 58 More actions · Answer 12

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 SSC Guru Points: 150467 More actions · Answer 13

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
SQLkiwi blog
@SQL_Kiwi

AlreadyPicked SSCommitted Points: 1786 More actions · Answer 14

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.