SET ANSI_NULLS ON/OFF & SET QUOTED_IDENTIFIER ON/OFF in SQL Server ?

Hi,

SET ANSI_NULLS ON / OFF
GO

SET QUOTED_IDENTIFIER ON / OFF
GO

These we can notify at creating new Stored Procedure in Sql Server.

ANSI Stands for American National Standards Institute .

When to use ON / OFF.

For Example i had a table named "Person.Address"

My Table data looks like when i used a simple select query.

Query 1: 

SELECT     AddressID, AddressLine1, AddressLine2, City
FROM   Person.Address

Result :

Now i am using SET ANSI_NULLS ON :

Query 2:

SET ANSI_NULLS ON

SELECT  AddressID, AddressLine1, AddressLine2, City

FROM   Person.Address

WHERE AddressLine2 < > NULL

AND AddressLine2 = NULL

Result :

Note :

When  SET ANSI_NULLS ON, Equals (=), not equals (< >) comparison against a null value in WHERE condition in SELECT Statement it returns zero rows even if the column contains NULL values or any data. 

Because it will not follow the SQL-92 standard.

Now i am using SET ANSI_NULLS OFF :

Query 3:

SET ANSI_NULLS OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

Query 4:

SET ANSI_NULLS OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  Person.Address

WHERE AddressLine2 = NULL

Result :

Note :

When  SET ANSI_NULLS OFF, Equals (=), not equals (< >) comparison against a null value in WHERE condition in SELECT Statement it returns if the column contains any null values when (=), and it returns if the column contains any data when (< >).

Because it follows the SQL-92 standard.

Same way we can use these ANSI_NULLS {ON/OFF} in creating Stored Procedures.

===============================================================

SET QUOTED_IDENTIFIER ON / OFF

In SET QUOTED_IDENTIFIER ON / OFF

When it is ON, Any character set in double quotes (" ") treated as T-SQL Identifier like (SP Name, Table Name and Column Name etc .....). Here i am giving column name in double quotes.

For Example : Same Table

Query 5:

SET QUOTED_IDENTIFIER ON

SELECT AddressID, AddressLine1, "AddressLine2", City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

When it is OFF, it will be treated as string, and it gives that string value in quotes as a result with no column name, but in case if table it will throw the error like Incorrect syntax near Table Name.

Query 6: For Column Name in double quotes

SET QUOTED_IDENTIFIER OFF

SELECT AddressID, AddressLine1, "AddressLine2", City

FROM  Person.Address

WHERE AddressLine2 < > NULL

Result :

Query 7 : For Table Name in double quotes

SET QUOTED_IDENTIFIER OFF

SELECT AddressID, AddressLine1, AddressLine2, City

FROM  "Person.Address"

WHERE AddressLine2 < > NULL

Result :

I hope you will enjoy this information

Thanks & Regards 

RAJ