When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ANSI NULL ON/OFF:
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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
I have a client database in which I’m trying to create an indexed view. For unknown reasons one particular table has the Ansi_nulls property set to false! How can this be updated. SQL 2008 R2.
Any help would be greatly appreciated.
Lorna
Hi Please try this
set ansi_nulls on
select * from myTab where [name] is null
we will see result with name value is null
select * from myTab where [name] = null
we will not any see result
set ansi_nulls off
select * from myTab where [name] is null
we will see result with name value is null
select * from myTab where [name] = null
we will see result with name value is null
Hi Pinal,
Could you please explain about set quoted identifier on/off with example?
Thank you
Hi
See This
SET ANSI_NULLS ON
IF NULL = NULL
PRINT ‘same’
ELSE
PRINT ‘different’
–result: different
SET ANSI_NULLS ON
IF NULL IS NULL
PRINT ‘same’
ELSE
PRINT ‘different’
— result: same SET ANSI_NULLS OFF
IF NULL = NULL
PRINT ‘same’
ELSE
PRINT ‘different’
Thank you
Very simple and easy to grab the meaning of ANSI_NULLS. thank you
Nice post……………
Hi iam a dot net fresher how i can improve my coding language for reference.
if we set ‘ SET ANSI_NULLS ON’
it doesn’t give records that contains null values is it..?
for eg: select * from employee where emp_city= null
The above query doesn’t give any record is it but if i use ‘is null’ in place of null i got the records that contain null values. Then tell me exactly what is the use of ‘ANSI_NULL is set to ON’
When I was trying to execute the following qureies it is not displaying the values
Create table color
(
Id int primary key,
Color varchar(100) default null
)
Insert into color values (1,’green’)
Insert into color values (2,’blue’)
Insert into color values (3,’black’)
Declare @s1 varchar(100),
@s2 varchar(100)
Set quoted_idenifier off
Select @s1 = ‘bg’
Select @s2 = “‘[“+@s1+”]%’”
Select @s2
Select * from colors where name like @s2
If u execute the query it will not return a values but if u copy the output of @s2 and place it in a name like “[bg]%” it retrieves result .. Pls answer as son a u can
Select * from colors where “name” like @s2 why you use name, and the table name also wrong. correct Query is
Select * from color where color like @s2
Do I need to do this when creating stored procedures or functions? If I have a generated database script I use to create database instances, do I need this before and after every statement or can it simply be executed once at the top of the script (and have scripts separated by GO)?