Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF
10 Sunday Jan 2010
Written by Ranjith in SQL Server
Tags
SET ANSI_NULL OFF, SET ANSI_NULL ON, SET QUOTED_IDENTIFIER OFF, SET QUOTED_IDENTIFIER ON
Share it
These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?
Below is the typical usage of these options.
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE SampleProcedure AS BEGIN -- select employees SELECT * FROM HumanResources.Employee END
Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.
SET QUOTED_IDENTIFIER ON/OFF:
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.
SET QUOTED_IDENTIFIER ON CREATE TABLE "SELECT" ("TABLE" int) -- SUCCESS GO
SET QUOTED_IDENTIFIER ON SELECT "sometext" AS Value -- FAIL because “sometext” is not a literal
Though the “SELECT” and “TABLE” are reserved keywords we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.
When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.
SET QUOTED_IDENTIFIER OFF CREATE TABLE "SELECT"(“TABLE” int) -- FAIL GO
SET QUOTED_IDENTIFIER OFF SELECT "sometext" AS Value -- SUCCESS as “sometext” is treated as a literal
You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal. The default behavior is ON in any database.
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' --result: same (now NULL = NULL works as 1=1)
The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.
Why are these two options Special?:
These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure,Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.
You can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.
SELECT uses_ansi_nulls, uses_quoted_identifier FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')
And if you need to guarantee the behavior for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure are only applicable until the procedure completes its execution.
Hope it helps.
– Ranjith
73 comments
Nazir Ahmed said:
June 25, 2010 at 9:15 am
Thanks a lot for this valuable information. Brilliantly explained.
Cheers,
Nazir Ahmed
Ranjith said:
June 28, 2010 at 11:05 am
Thanks for visiting and for kind words Nazir Ahmed
Harsha said:
July 6, 2010 at 3:03 pm
Good examples….
Mayur Shendge said:
July 23, 2010 at 7:14 am
Nice article…
Ashoo said:
July 24, 2010 at 5:43 pm
Nice explanation… thanks for the article..
Ranjith said:
July 25, 2010 at 2:46 am
Thanks Ashoo and Mayur. I am glad that it helped.
sundeep said:
July 27, 2010 at 3:14 am
Very nice explanation…good examples
CLF said:
October 28, 2010 at 12:25 am
I turn quoted_identifier off when I write dynamic SQL so that I can use single quotes in the query without escaping them. ” select foo from bar where character = ‘a’ “
Mohsin said:
November 24, 2010 at 10:50 am
Excellent article…
sandhya said:
January 13, 2011 at 10:26 pm
Very well explained. Thank you.
Mohan Kumar said:
January 15, 2011 at 12:51 pm
Good Article………
Hemant Ghiya said:
February 14, 2011 at 5:31 pm
Nice Article buddy…………..
Suresh kannan said:
April 27, 2011 at 10:35 am
Ranjith u r Excellent.
Sonal said:
May 19, 2011 at 9:55 am
Nice Article
Ranjith said:
May 19, 2011 at 6:03 pm
Thank you all for the feedback.
Adam Mendoza said:
May 27, 2011 at 6:36 pm
Is there any performance difference with either approach?
Ranjith said:
May 28, 2011 at 5:25 pm
Hi Adam,
Though I haven’t tested on performance aspect, I don’t think there will be any performance difference that anyone could care about.
Thanks.
Surendra said:
June 29, 2011 at 11:58 am
Good Article…
sathish said:
July 10, 2011 at 12:54 pm
Good and simple example
Mrinmoy Kundu said:
July 7, 2011 at 12:42 pm
Very good article and very easily explained. Thanks buddy
Bharath reddy said:
July 26, 2011 at 9:32 am
Very nice example
srikiran said:
September 19, 2011 at 7:18 am
Very good article and very nice explanation
Manoj said:
October 4, 2011 at 5:14 am
Dear Ranjith This was a excellent Article if it has an example in Demo format it Will be more useful for us
Ranjith said:
October 4, 2011 at 10:30 am
Thanks Manoj for the feedback.
Shrikant Bijapurkar said:
October 31, 2011 at 8:18 am
Nicely explained.
This helped.
Thanks!!
Saikat Malakar said:
November 11, 2011 at 8:31 am
Dear Ranjith Kumar,
Your article was too good.Now i can easily feel the difference.Your Explanation was fantastic.Will you provide me some article on sp-output parameter and triggers with example.Please reply in my mail id.
Thanks in previous.
Mandana said:
January 20, 2012 at 7:55 pm
Great explanation! Thank you, Ranjith for sharing! I read this subject in several “thick books” and had a hard time to make a clear understanding. I think you should write books in technical subjects, if you haven’t already. If you have please let us know the name.
ranji842 said:
January 23, 2012 at 5:52 pm
Thanks Mandana. This is the best comment I have got so far and you definitely made me think about writing more. I will try to find sometime for this
fatemeh said:
January 23, 2012 at 7:17 am
thanks
🙂
Bob Kolman said:
January 23, 2012 at 10:28 pm
Thanks for the great tutorial. I believe you have a cut/paste
typo in your example for ANSI_NULLS, whereby the 2nd case should SET ANSI_NULLS OFF.
SET ANSI_NULLS ON
IF NULL = NULL
PRINT ‘same’
ELSE
PRINT ‘different’
–result: different
SET ANSI_NULLS ON <<– TYPO – should be OFF
IF NULL IS NULL
PRINT 'same'
ELSE
PRINT 'different'
— result: same
Thanks again.
ranji842 said:
January 26, 2012 at 8:50 am
Thanks Bob, I have corrected the typo
Per Löfgren said:
January 26, 2012 at 9:08 am
I don’t think it’s a typo. He shows how to properly compare against NULL when the ANSI_NULLS is ON.
(look in the IF expression, and the result output).
He might have added a third case showing ansi off like:
SET ANSI_NULLS OFF
IF NULL = NULL
PRINT ‘same’
ELSE
PRINT ‘different’
–result: same
ranji842 said:
January 26, 2012 at 5:18 pm
Thanks Per! You are right on the typo, And to remove confusion I have also added the ANSI_NULLS OFF example
pradeep said:
March 2, 2012 at 5:49 am
this one is great explanation…thanks .
pradeep
Sako73 said:
March 12, 2012 at 3:43 pm
Nice explanation. Thanks.
Oliver said:
March 14, 2012 at 11:59 am
Thanks for the article, it was very helpful.
KIRAN said:
April 23, 2012 at 5:51 am
Great.Learnt a lot.
Deepan said:
July 24, 2012 at 9:35 pm
Clear explanation.Thanks
P Naveen said:
August 30, 2012 at 9:47 am
How to find a quotient between two numbers in SQL SERVER 2008,do v have any function to do so ..?
Please do reply …
Ranjith said:
September 4, 2012 at 7:09 am
SELECT 1000/10
Ans: 100
Is that is what you are looking for?
Konveyance said:
August 13, 2014 at 8:03 pm
DAX has a QUOTIENT function… 🙂
And also, you can use modulo operator to get remainders:
select 7%3 as modulo;
–returns 1
select 8%3 as modulo;
–returns 2
other than that — Ranjith is correct.
Anwaar Ahmed said:
November 10, 2012 at 7:00 am
Good yar.. Excellent
Savaş said:
November 16, 2012 at 2:04 pm
Thanks a lot for those clear information.
This explanation is the clearest and the best explenation in the sites i could found. Also it gives complete information aabout the topic.
Ranjith said:
November 29, 2012 at 11:42 am
Thanks Savas !!!
saiteja said:
November 19, 2012 at 2:32 am
very nice explanation…..
Sanjay Rathod said:
November 21, 2012 at 6:31 am
Excellent Article !
And as specially thanks for describing use of “sys.sql_modules” table to get procedure / trigger / function settings for these options.
Bharath said:
December 24, 2012 at 9:07 am
Nice Article :):)
Komathi said:
January 8, 2013 at 6:58 am
Nice articles explained with examples and metadata table. Good work.
Aaron Whigham said:
March 11, 2013 at 2:28 am
Excellent Tutorial. Thank you.
Rizwan Gazi said:
June 27, 2013 at 9:37 am
Very simple and clear explanation with example. Thanks
venkatappa said:
July 11, 2013 at 2:53 pm
good one
Manoj said:
September 15, 2013 at 1:13 pm
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
Ranjith said:
September 25, 2013 at 5:38 am
You have added an extra single quote when assigning value to @s2, I have corrected it as below which works as expected
Declare @s1 varchar(100),
@s2 varchar(100)
SET QUOTED_IDENTIFIER OFF
Select @s1 = ‘bg’
Select @s2 = “[“+@s1+”]%”
Select @s2
Select * from color where Color like @s2
Deepak said:
February 10, 2014 at 6:23 am
I m extremely happy to read such a clear view about my question with a perfect and valuable example too. Thanks from core of the brain:)
Badari said:
July 2, 2014 at 5:12 am
Good with clear explanation on both options
Suresh Sundar said:
July 17, 2014 at 5:16 am
Hi Ranjith,
when we need to use
ansi_nulls and
quoted_identifier, create table script or only select ,triggers,udf that kind of times only.
is it good to use create table script itself ansi_nulls,quoted_identifier ??
waiting for u r response asap
Ranjith said:
August 3, 2014 at 3:49 pm
Hi Suresh
Are you planning to create a table with same name as any reserved key word?
ex: Is you table name is “GROUP”?
In that case you can try with QUOTED_IDENTIFIER ON, otherwise no need when creating a table. But when creating a procedure you have to check what behavior your procedure wants and SET it based on that.
And for ANSI_NULL you can not set it from SQL 2008 , And I think by now you must be using > 2008 version, So do not worry about now.
Thanks,
Ranjith.
kesav said:
August 19, 2014 at 7:46 am
Is there any performance difference b/w when set QUOTED_IDENTIFIER ON and OFF ?
Qaiser Nadeem said:
February 2, 2015 at 10:46 pm
Kudos…good explanation and examples….
Thanks Ranjith
K K said:
April 17, 2015 at 10:00 am
Thnx a lot.Nice explanation with example..
Gaurav Nakil said:
May 26, 2015 at 8:22 am
Thanks a lot,
perfect and to the point ., nice simple examples.
Arman khan said:
August 31, 2015 at 7:19 am
Very well explained…to the point
Sushant Anand said:
October 7, 2015 at 7:16 am
Nice Description with ideal examples.
Good job Mr. Ranjith Kumar.
thanks
vijay said:
October 30, 2015 at 11:39 am
good explain of procedure struture
Sush said:
December 2, 2016 at 7:22 am
Informative Article!!
Sam said:
January 27, 2017 at 3:22 am
Great article! Thank you.
srikanth said:
March 28, 2017 at 8:25 am
Very nice and clear explanation
Ayub Jani Shaik said:
April 29, 2019 at 4:32 am
Awesome explanation. This is the first article, which gave me a clear picture.
ishaboy said:
July 9, 2019 at 8:50 pm
Ishaboy and i just wanted to thank you. i’d read articles on both, but there were lines in this article that definitely inspired some aha moments.
appreesh
Ranji842 said:
July 21, 2019 at 9:26 am
Thanks!, Good to know
Rizwan Gazi said:
February 23, 2020 at 7:52 am
Very simple, nice and clear explanation dear.
Just one query, if I set “QUOTED_IDENTIFIER OFF”, then is there any performance improvement?
jonas said:
March 12, 2020 at 12:21 am
thank you
Ayush said:
December 18, 2020 at 8:28 pm
Great work