Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

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/OFF:
The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL. And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.
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

75 comments

  1. Pingback: SqlServerKudos

  8. 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’ “

    Reply

    1. 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.

      Reply

  22. 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.

    Reply

  23. 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.

    Reply

    1. 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

      Reply

  25. 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.

    Reply

    2. 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

      Reply

  31. How to find a quotient between two numbers in SQL SERVER 2008,do v have any function to do so ..?
    Please do reply …

    Reply

    2. 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.

      Reply

  33. 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.

    Reply

  35. Excellent Article !

    And as specially thanks for describing use of “sys.sql_modules” table to get procedure / trigger / function settings for these options.

    Reply
  38. Pingback: [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1 | Alec Irwin.com

  42. 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

    Reply

    1. 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

      Reply

  43. 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:)

    Reply

  45. 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

    Reply

    1. 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.

      Reply

  57. 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

    Reply

  58. Very simple, nice and clear explanation dear.
    Just one query, if I set “QUOTED_IDENTIFIER OFF”, then is there any performance improvement?

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *