CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Philip
Philip

Reputation: 21

SET ANSI NULLS ON

What is the difference between SET ANSI NULLS ON and SET ANSI NULLS OFF?

I understand that the operators = and <> in where statement for comparing with nulls does not return any value when SET ANSI NULLS ON is mentioned. As a best practice which statement I should use in procedures? and why?

Regards, Philip

Upvotes: 0

Views: 535

Answers (3)

Hell Boy
Hell Boy

Reputation: 981

SET ANSI NULLS ON Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values

Key Point: As Set ANSI NULLS mainly Deals with ATOMICITY of the database you should SET ANSI_NULLS ON

Read "ATOMIC WITH" block in this link

Upvotes: 1

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

Best practice is set ANSI_NULLS always ON according to Microsoft.

MSDN says

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So I would start practicing my query with ANSI NULL ON from now on.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

As the documentation states:

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

ON is the default, and is compatible with other databases. Don't bother turning it off, unless you have a really, really good reason.

Upvotes: 0

Related Questions