Big Data, Data Analytics and Data Science | BI-BIGDATA.com

Most of us use those statements while creating stored procedures and functions. most of us don’t know what is the significance of those statements and how they are useful.

SET QUOTED_IDENTIFIER ON/OFF

Syntax :

SET QUOTED_IDENTIFIER { ON | OFF }

This 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

The default behavior is ON in any database.

Reference url: http://msdn.microsoft.com/en-us/library/ms174393.aspx

SET ANSI_NULLS ON/OFF

Syntax:

            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.

-- Create table t1 and insert values.

CREATE TABLE t1 (a INT NULL)

INSERT INTO t1 values (NULL)

INSERT INTO t1 values (0)

INSERT INTO t1 values (1)

GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting'

DECLARE @varname int

SELECT @varname = NULL

SELECT * FROM t1 WHERE a = @varname

SELECT * FROM t1 WHERE a <> @varname

SELECT * FROM t1 WHERE a IS NULL

GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON'

SET ANSI_NULLS ON

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT * FROM t1 WHERE a = @varname

SELECT * FROM t1 WHERE a <> @varname

SELECT * FROM t1 WHERE a IS NULL

GO

-- SET ANSI_NULLS to OFF and test.

PRINT 'Testing SET ANSI_NULLS OFF'

SET ANSI_NULLS OFF

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT * FROM t1 WHERE a = @varname

SELECT * FROM t1 WHERE a <> @varname

SELECT * FROM t1 WHERE a IS NULL

GO

-- Drop table t1.

DROP TABLE t1

Reference url: http://msdn.microsoft.com/en-us/library/ms188048.aspx

Thanks

Yashwant