meaning of SET ANSI_NULLS ON

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Hi Friends....

I want to know the meaning of

VB

SET ANSI_NULLS ON     

AND

SET QUOTED_IDENTIFIER ON

Posted 4-May-12 19:15pm

Arul R Ece

Add a Solution

3 solutions

Solution 2

Refer these:

http://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-means[^]

http://msdn.microsoft.com/en-us/library/ms188048.aspx[^]

Posted 4-May-12 19:25pm

sravani.v

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thursunamy · Accepted Answer · 2012-05-04T19:21:00

You can check from the links

http://blog.sqlauthority.com/2007/03/05/sql-server-quoted_identifier-onoff-and-ansi_null-onoff-explanation/[^]

http://praveenbattula.blogspot.com/2009/05/use-of-set-ansinulls-on-in-sql-server.html

ANSI_NULLS ON

This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

uspatel · Accepted Answer · 2012-05-04T19:33:00

When

SQL

SET ANSI_NULLS ON

it means ISO Standard is being followed.
means
= and <> should not be used for null comparison.

If you want to use = or <> for null comparison use

SQL

SET ANSI_NULLS OFF

it means do not follow ISO Standard.

SQL

SET ANSI_NULLS

should be set to ON for executing distributed queries
for maintaining compatibility of queries accross Servers.

and

QUOTED_IDENTIFIER
It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes. When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a literal.

meaning of SET ANSI_NULLS ON

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0