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:
Post a Comment