Stored Procedure QUOTED IDENTIFIER, ANSI NULLS and NOCOUNT

While working on stored procedures in SQL Server I came across a few keywords which are not commonly used while writing queries: QUOTED IDENTIFIER ON/OFF, ANSI NULLS ON/OFF and NOCOUNT ON/OFF.

I had created a new stored procedure (by right clicking on Stored Procedures folder in left pane of SSMS and then “New Stored Procedure”). I tried working on it, but my curiosity wouldn’t let me finish without figuring out what these settings are do and whether they are important.

1) QUOTED IDENTIFIER ON/OFF

The syntax is:

[code lang=”sql”]SET QUOTED_IDENTIFIER ON[/code]

or

[code lang=”sql”]SET QUOTED_IDENTIFIER OFF[/code]

.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

You can read more about it on http://msdn.microsoft.com/en-us/library/ms174393.aspx.

2) ANSI NULLS ON/OFF

The syntax is

[code lang=”sql”]SET ANSI_NULLS ON[/code]

or

[code lang=”sql”]SET ANSI_NULLS OFF[/code]

.

For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure.

Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its original value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

When this is ON, any query that compares a value with a NULL returns a False.

When OFF, any query that compares a value with a NULL returns a NULL value.

You can read more about it on http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx.

3) NOCOUNT ON/OFF

The syntax is

[code lang=”sql”]SET NOCOUNT ON[/code]

or

[code lang=”sql”]SET NOCOUNT OFF[/code]

.

When this is OFF, it will display the count of rows Inserted/Deleted/Updated.

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

You can read more about it on http://msdn.microsoft.com/en-us/library/ms189837.aspx.