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
Tags: SET ANSI_NULL OFF, SET ANSI_NULL ON, SET QUOTED_IDENTIFIER OFF, SET QUOTED_IDENTIFIER ON
Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF « ranjithk’s blog…
Kudos for a great Sql Server article – Trackback from SqlServerKudos…
Thanks a lot for this valuable information. Brilliantly explained.
Cheers,
Nazir Ahmed
Thanks for visiting and for kind words Nazir Ahmed
Good examples….
Nice article…
Nice explanation… thanks for the article..
Thanks Ashoo and Mayur. I am glad that it helped.
Very nice explanation…good examples
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’ “
Excellent article…
Very well explained. Thank you.
Good Article………
Nice Article buddy…………..
Ranjith u r Excellent.
Nice Article
Thank you all for the feedback.
Is there any performance difference with either approach?
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.
Good Article…
Good and simple example
Very good article and very easily explained. Thanks buddy
Very nice example
Very good article and very nice explanation
Dear Ranjith This was a excellent Article if it has an example in Demo format it Will be more useful for us
Thanks Manoj for the feedback.
Nicely explained.
This helped.
Thanks!!
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.
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.
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
thanks
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.
Thanks Bob, I have corrected the typo
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
Thanks Per! You are right on the typo, And to remove confusion I have also added the ANSI_NULLS OFF example