SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

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 (http://blog.SQLAuthority.com)

About these ads

38 thoughts on “SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

    • SET QUOTED_IDENTIFIER OFF
      GO
      — An attempt to create a table with a reserved keyword as a name
      — should fail.
      CREATE TABLE “select” (“identity” INT IDENTITY NOT NULL, “order” INT NOT NULL);
      GO

      SET QUOTED_IDENTIFIER ON;
      GO

      — Will succeed.
      CREATE TABLE “select” (“identity” INT IDENTITY NOT NULL, “order” INT NOT NULL);
      GO

      SELECT “identity”,”order”
      FROM “select”
      ORDER BY “order”;
      GO

      DROP TABLE “SELECT”;
      GO

      SET QUOTED_IDENTIFIER OFF;
      GO

      Like

  1. Got it… I have Created a table with fields Id and Name then i inserted many values in that table and also inserted a NULL value. in the Name field
    Then When i am rnning below querry it not returns any record to me.
    SET ANSI_NULLS ON
    SELECT Name FROM MyTable(Nolock) WHERE Name=NULL

    AND
    When iam running below querry it returns the row with NULL value
    SET ANSI_NULLS OFF
    SELECT Name FROM MyTable(Nolock) WHERE Name=NULL

    So, If in case of null when ANSI_NULLS is on Wht are the benefits of this i think this is wrong caz it does not return any row which having null. Why this is present in SQL any Specific reasons, Where we use this.????

    Like

  2. I’m a SQL beginner, and this stuff drives me crazy. However, we’ve using “is null” instead of “= null” and it seems to work regardless of the setting. Try this. This shows the difference between the two approaches:

    set ansi_nulls on

    declare @myTab table (id int null, [name] varchar(10) null)

    insert into @myTab (id,[name]) values (1, ‘sam’)
    insert into @myTab (id,[name]) values (2, ‘dave’)
    insert into @myTab (id,[name]) values (3, null)

    select * from @myTab
    select * from @myTab where [name] is null
    select * from @myTab where [name] = null

    Like

  3. Hi
    in a query set ansi_nulls on
    and select the null coulmn is gives null value
    if set ansi_nulls off also behave same.
    then how to know diff between the ansi_nulls on and off
    do mail to me.

    Thank you

    Like

  4. When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. (FALSE)
    When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL
    When SET ANSI_NULLS is ON,

    a SELECT statement using WHERE Emp_Name= NULL returns zero rows even if there are null values in Emp_Name.

    A SELECT statement using WHERE Emp_Name NULL returns zero rows even if there are nonnull values in Emp_Name.

    When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To () comparison operators do not follow the SQL-92 standard.

    A SELECT statement using WHERE Emp_Name= NULL returns the rows with null values Emp_Name.

    A SELECT statement using WHERE Emp_Name NULL returns the rows with nonnull values in the column.

    SET ANSI_NULLS also must be ON for executing distributed queries and when creating or manipulating indexes on computed columns or indexed views.

    If SET ANSI_NULLS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error listing all SET options violating the required values.

    When executing a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select as though there were no such indexes on the tables or views.

    If you want a script to work regardless of the ANSI NULSS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values.

    Like

  5. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 4 Journey to SQL Authority with Pinal Dave

  7. SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation:

    i am using SQL server 2005
    in affect to what is said about ANSI NULL ON and
    QUOTED IDENTIFIER ON,
    I do not get NULL if i place empty records in tables
    instead only an empty space appears

    please givme more details on these

    Like

  8. When I run queries using sql 2005 the file output is encoded UCS-2, when I run the same query on my old desktop using sql 2000 the files are ANSI. Same database, different desktop and different pcs. I have set my file output to the normal defaults on the sql 2005 pc. What am I missing?

    Like

  9. Hi Pinal,
    why do you know about null values dont work in SHAPE – APPEND – CALC.

    example;
    ———————-
    shape
    a_table…
    append a1_table bla bla… relation a to a1 …
    append a2_table bla bla… relation a to a2 …
    compute
    sum (a1_money) as a1m,
    sum (a2_money) as a2m,
    calc (a1m – a2m) as adiff
    —————————

    “adiff” is empty if “a2m” is null…

    ANSI_NULL is not work in these query.

    do you have any solution ?
    please help me immediately.

    thank you.

    Like

  10. SET ASNI_NULL ON
    select * from table where col1 = NULL
    select * from table where col1 NULL
    both the cases result is 0 rows

    SET ASNI_NULL OFF
    select * from table where col1 = NULL
    select * from table where col1 NULL
    in one case definately you will get result

    Hope this explanation helps. I found this from a forum and tried. I worked fine. So why not you guys try.

    Like

  11. hi HariPrasad,
    I have created a table
    the result set of SELECT * FROM table is as follows

    1 Steve
    2 NULL
    3 Mike
    4 NULL

    When I used
    SET ANSI_NULLS ON
    select * from table
    the result set is same as the above.
    — But when I used
    SET ANSI_NULLS ON
    select Name from table where Name = NULL
    It retrieves no result.
    I did not understand why? there is nothing difference between setting the ANSI_NULLS ON and ANSI_NULLS OFF
    in case of
    SET ANSI_NULLS ON
    select * from table

    and

    SET ANSI_NULLS ON
    select * from table

    Can u explain ?

    Like

    • Hi Santosh,
      Sorry just saw your post. Setting ANSI_NULLS ON or OFF is used for selecting NULL values.

      Eg.. in your case the select * from table irrespective of the ANSI flag will result in
      1 Steve
      2 NULL
      3 Mike
      4 NULL

      Try these two scenarios.
      1. SET ANSI_NULLS ON
      select * from table where name = NULL
      2. SET ANSI_NULLS OFF
      select * from table where name = NULL

      Now I am sure you will find the difference.. Let me know how are you getting on this.

      Cheers
      Hari

      Like

  12. Does anybody know how to suppress the “SET ANSI_NULLS SET QUOTED_IDENTIFIER ON” lines when I modify a stored procedure in Management Studio? Thanks!

    Like

  13. Hi

    I am trying to create a stored procedure for SQL Server 2008 R2. I want this stored procedure to have uses_ansi_nulls as False.
    My database’s ANSI NULLS Enabled property is set to False.
    I’m creating procedure as below:

    SET ANSI_NULLS ON
    GO
    CREATE PROCEDURE MY_SP

    Procedure is created successfully but following query returns true.

    SELECT uses_ansi_nulls, uses_quoted_identifier
    FROM sys.sql_modules
    WHERE object_id = object_id(‘MY_SP’)

    Is there a way to create stored procedure so that above query returns false (SET ANSI_NULLS remain false).

    Like

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

    Like

  15. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31 Journey to SQLAuthority

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

    Like

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

    Like

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

  19. 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’

    Like

  20. Pingback: SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052 | Journey to SQL Authority with Pinal Dave

  21. Pingback: SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage | Journey to SQL Authority with Pinal Dave

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s