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]
[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]
[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]
[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.