ANSI_NULLS

Question

ANSI_NULLS

Viewing 15 posts - 31 through 45 (of 81 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Paul White NZ (4/6/2010)
CirquedeSQLeil (4/6/2010)
And yes, by having Ansi_Nulls Off, I can see there being complications. I will have to verify this setting on my servers.
SELECT object_type = OBJ.type_desc,
[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),
[object_name] = OBJ.name,
SM.[definition]
FROM sys.objects OBJ
JOIN sys.sql_modules SM
ON SM.[object_id] = OBJ.[object_id]
WHERE OBJ.type_desc IN
(
N'SQL_STORED_PROCEDURE',
N'SQL_SCALAR_FUNCTION',
N'SQL_INLINE_TABLE_VALUED_FUNCTION',
N'SQL_TABLE_VALUED_FUNCTION',
N'SQL_TRIGGER',
N'VIEW'
)
AND SM.uses_ansi_nulls = 0
AND OBJ.is_ms_shipped = 0
ORDER BY
object_type,
[schema_name],
[object_name];

Quite a nice snippet of code.

The place where I had the biggest concern was confirmed by this script.

Objects created by CRM (Microsoft) turn ansi_nulls off in quite a few places. It doesn't look pretty.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Paul White NZ (4/7/2010)
Toreador (4/7/2010)
Is there any situation where setting ansi_nulls off is a good idea?
I have never come across one. Apart from the bizarre rules illustrated by the question, ANSI_NULLS OFF is going away in a future version. Attempting to set ANSI_NULLS OFF will generate an error message in that future version. Will be a huge change for some people.

Which in turn will break one of Microsoft's own products - Dynamix / CRM

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Paul,

I hope you don't mind. I took your great script and made a modification to it. I am posting it here. This mod version makes reading the "definition" column easier to read by using XML Path.

SELECT object_type = OBJ.type_desc,

[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),

[object_name] = OBJ.name,

(Select [definition] AS [text()]

From sys.sql_modules

Where [object_id] = OBJ.[object_id]

FOR XML PATH(''), TYPE) as ProcDef

FROM sys.objects OBJ

JOIN sys.sql_modules SM

ON SM.[object_id] = OBJ.[object_id]

WHERE OBJ.type_desc IN

(

N'SQL_STORED_PROCEDURE',

N'SQL_SCALAR_FUNCTION',

N'SQL_INLINE_TABLE_VALUED_FUNCTION',

N'SQL_TABLE_VALUED_FUNCTION',

N'SQL_TRIGGER',

N'VIEW'

)

AND SM.uses_ansi_nulls = 0

AND OBJ.is_ms_shipped = 0

ORDER BY

object_type,

[schema_name],

[object_name];

There are some display issues with < > type symbols - but I think it is quite useful.

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

weitzera Hall of Fame Points: 3442 More actions · Answer 4

When I first came to use SQL, I was flabbergasted that anyone could think it wise to design a system where x=y could be false and x <> y could also be false. Nevertheless, it was beaten into me, and I adapted. I have never tried turning that setting off, because I always fell that sticking to standards will help you as a developer in the long run. Of course I got this question wrong, and now I'm even more flabbergasted that turning off this setting makes _less_ sense (to the uninitiated) than the regular null weirdness! Well, live and learn... Thanks for the great question Paul.

-a.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Heh. I have to admit that I got it wrong too. I haven't done anything with ANSI_NULLs disabled in a very, very long time, and I couldn't decide if looking up the setting effects would be cheating or not... so I didn't and yup, only got 50% of the options right (which means 0% of the question entire).

Good, tough question Paul. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Paul:

Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

RBarryYoung (4/7/2010)
Paul:
Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.

Out of curiosity, I went back to check how I did on that one as well. I am surprised that I did not leave a comment. I don't recall when I answered it, but I got it correct.

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

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 8

Paul,

Good question, great discussion, especially about the execution plans. Glad to hear that ANSI NULLS OFF will be deprecated. (scary stuff!) (My gut feeling was that it was allowed to get desired results on negative comparisons. Having to specifically check for null values is tedious, but much clearer in the long run.)

CirquedeSQLeil (4/7/2010)
Paul,
I hope you don't mind. I took your great script and made a modification to it. I am posting it here. This mod version makes reading the "definition" column easier to read by using XML Path.

Thanks to both of you for the very nice script.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Carla,

You're welcome - it was really Paul's script. I just added a little mod to it.

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 10

CirquedeSQLeil (4/7/2010)
Which in turn will break one of Microsoft's own products - Dynamix / CRM

I've worked with that for14 years, and I never noticed.

Good explanation Paul.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

sknox SSChampion Points: 12388 More actions · Answer 11

Paul White NZ (4/7/2010)
honza.mf (4/7/2010)
There is only one complication with negative queries I mentioned above. Easy to solve but hard to explain to (some) non-developpers. "I want to see all records that don't have..."
Quite, but OFF is deprecated, so we don't want to be using that in new work, right? 😉
Don't get me started on how NULLs complicate queries and designs unnecessarily...:hehe:

What's MORE important is how NULLS improve queries and and designs when used properly and when necessary. Sadly that's usually the exception...

Add me to the list of those who will cheer when the SET ANSI_NULLS OFF aberration is finally gone.

For the record, I failed the test -- I missed the part about it only working when at least one side is a NULL variable or literal NULL. :blush:

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 12

CirquedeSQLeil (4/7/2010)
Paul,
I hope you don't mind. I took your great script and made a modification to it. I am posting it here. This mod version makes reading the "definition" column easier to read by using XML Path.
SELECT object_type = OBJ.type_desc,
[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),
[object_name] = OBJ.name,
(Select [definition] AS [text()]
From sys.sql_modules
Where [object_id] = OBJ.[object_id]
FOR XML PATH(''), TYPE) as ProcDef
FROM sys.objects OBJ
JOIN sys.sql_modules SM
ON SM.[object_id] = OBJ.[object_id]
WHERE OBJ.type_desc IN
(
N'SQL_STORED_PROCEDURE',
N'SQL_SCALAR_FUNCTION',
N'SQL_INLINE_TABLE_VALUED_FUNCTION',
N'SQL_TABLE_VALUED_FUNCTION',
N'SQL_TRIGGER',
N'VIEW'
)
AND SM.uses_ansi_nulls = 0
AND OBJ.is_ms_shipped = 0
ORDER BY
object_type,
[schema_name],
[object_name];
There are some display issues with < > type symbols - but I think it is quite useful.

Thanks Paul for the QOD, I didn't know. Also thanks for the code and you too Jason for the for xml path change, I didn't know this either and am going to start using that in other areas. I just feel like I don't know anything today. 🙂

@jason, I too found it in Microsoft CRM, this doesn't surprise me there is always weird stuff in the CRM.

Why would anyone use Ansi Nulls off?

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Trey Staker (4/7/2010)
@jason, I too found it in Microsoft CRM, this doesn't surprise me there is always weird stuff in the CRM.
Why would anyone use Ansi Nulls off?

First, you're welcome.

Second in response to the CRM - I agree that there are tons of hokey things in it. However, I do like it a lot better than some of the other options out there. I'm curious what effects these ansi_nulls off settings in the CRM procs and functions has that requires it to be that way.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 14

RBarryYoung (4/7/2010)
Paul:
Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.

It's not surprising that it got a low percentage: ^ is an exponentiation operator in several programing languages, and the first option in the answer pushes people towards thinking of that (that's how you got me on that one) and forgetting what ^ is in T-SQL. If about half the people who selected that option were caught like that, and would have got it right otherwise, that one catch brings the success rate down by quite a large amount.

It's actually a brilliant question, because it penalises people who just jump in without thinking properly.

Tom

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

Thanks, Tom. I actually spent quite a lot of time on it. I especially liked that though it was T-SQL question, cutting and pasting it to a query window was no help at all.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]