Tuesday, 13 February 2018

SET QUOTED IDENTIFIER ON/OFF and ANSI NULLS ON/OFF in SQL Server

The SET QUOTED IDENTIFIER and ANSI NULLS are probably not used while writing a stored procedure, user function or triggers in SQL Server by beginners in DBA or SQL development. However, even without them, the results will be generated, but in case if you specify the SET QUOTED IDENTIFIER and ANSI NULLS, there could be fewer errors relating to quotes and NULL value comparisons respectively. Here are some reasons for setting these before to start to write stored procedures, functions or triggers.

SET QUOTED IDENTIFIER ON/OFF:

Setting the SET QUOTED IDENTIFIER ON can emphasize any data defined in double-quotes as either table name, column name, function name or procedure name. While defining the data in single quotes, it emphasizes data to be literal and can lead to an error. Let’s look at an example.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "TEST_TABLE" --Success

(ID int,
"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "TEST_TABLE" -- Fails since it takes the data entered as literal
(ID int,
"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER ON
GO
SELECT "Function_Dummy" --Fails since it takes the data entered as literal

SET QUOTED_IDENTIFIER OFF
GO
SELECT "Function_Dummy" --Success

ANSI NULL ON/OFF:

The ANSI NULL ON/OFF emphasizes how SQL Server compares the NULL values.

When set to ON, if the value NULL is compared with a = or <> sign, then the result produced would be false. In other words, any value comparing with NULL returns a zero.

SET ANSI_NULLS ON
IF NULL=NULL
PRINT 'SQL'
ELSE
PRINT 'ARENA' -- ARENA is a false statement if set to ANSI_NULL ON

When set to OFF, if the value NULL is compared with a = or <> sign, then the result produced would be true. In other words, any value comparing with NULL returns a NULL value itself.

SET ANSI_NULLS OFF
IF NULL=NULL
PRINT 'SQL'   -- SQL is a true statement if set to ANSI_NULL OFF
ELSE
PRINT 'ARENA' 

0 comments: