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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Delete Duplicate Records – Rows
Next Post
SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running

Related Posts

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

    Reply
  • 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

    Reply
  • Hi Pinal,

    Could you please explain about set quoted identifier on/off with example?

    Thank you

    Reply
  • 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

    Reply
  • Nice post……………

    Reply
  • Hi iam a dot net fresher how i can improve my coding language for reference.

    Reply
  • 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’

    Reply
  • 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
    • 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

      Reply
  • 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)?

    Reply

Leave a Reply

Menu