February 12, 2014

Stored Procedure - QUOTED_IDENTIFIER ON/OFF, ANSI_NULL ON/OFF and NOCOUNT ON/OFF

Most of the SQl Developer uses these options while writing TSQL Stored Procedure/User defined function/Trigger. QUOTED_IDENTIFIER is implemented at the parse time where as ANSI_NULL and NOCOUNT is implemented at run time.

QUOTED_IDENTIFIER identifies the data based on single and double quotes. When the setting is set on, all the string that is defined inside double quotes will be interpreted as T-SQL object identifier like table name, procedure name, column name etc. This quoted identifier do not have to follow T-SQL rules and will be reserved keywords and can include characters not generally allower in TSQL identifiers. All the character set that is defined in the single quotes will be interpreted as a literal.

When QUOTED_IDENTIFIER is off, the string that is inside single or double quotation mark will be interpreted as literals.

For Example:

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" INT) -- SUCCESS

GO

SET QUOTED_IDENTIFIER ON
SELECT "SOMETEXT" AS VALUE -- FAILS BECAUSE SOMETEXT IS NOT A LITERAL


SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT" ("TABLE" INT) -- FAIL

GO

SET QUOTED_IDENTIFIER OFF
SELECT "SOMETEXT" AS VALUE -- FAILS BECAUSE "SOMETEXT" IS  A LITERAL

ANSI_NULL helps to handle the comparison operations with NULL values. When ANSI_NULL is ON, any comparison on a select statement using the equals (=) and not equals (<>) operators with NULL values returns zero rows even if there are null values in the column. it follows the ISO standard so you need to use IS NULl and IS NOT NULL so as to do comparison with NULL values.

When ANSI_NULL is OFF, then amu compasion with NULL value using the equals (=) and not equals (<>) operators with NULL values returns rows with NULL value for the equals operator and not NULL values for not equals operator. It does not follow ISO standard and returns true and false.

For Example:

SET ANSI_NULLS ON
IF NULL IS NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: TRUE

SET ANSI_NULLS ON
IF NULL = NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: FALSE

SET ANSI_NULLS OFF
IF NULL = NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: TRUE

NOCOUNT helps to stop the count of rows affected during the execution of stored procedures.

For Example:

CREATE PROCEDURE USP_NOCOUNT

SET NOCOUNT OFF

AS
BEGIN

    DECLARE @TEMP TABLE(ID INT)
 
    INSERT INTO @TEMP VALUES(1)
    INSERT INTO @TEMP VALUES(2)

    SELECT * FROM @TEMP

END

Then, execute the stored procedure  USP_NOCOUNT

Exec USP_NOCOUNT

After execution, you will get message twice "1 row(s) affected".

CREATE PROCEDURE USP_NOCOUNT

SET NOCOUNT ON

AS
BEGIN

    DECLARE @TEMP TABLE(ID INT)
 
    INSERT INTO @TEMP VALUES(1)
    INSERT INTO @TEMP VALUES(2)

    SELECT * FROM @TEMP

END

Then, execute the stored procedure  USP_NOCOUNT

Exec USP_NOCOUNT

After execution, you will get message "Command(s) completed successfully".

No comments: