SET ANSI_NULLS, NOCOUNT and QUOTED_IDENTIFIER.

ermrlehkara, 2015-12-30 (first published: 2015-12-17) 

Hi Guys,

When you generate a script to create a Stored Procedure, below three statements are generate by default.

SET ANSI_NULLS on

SET NOCOUNT on

SET QUOTED_IDENTIFIER on

Why do we need these. Why Management Studio thinks we would require these options. Let us understand each one of these one by one.

1. SET ANSI NULL ON :

I am sure you know what is SET and ON. Lets us understand ANSI and NULL.

ANSI is  “American National Standard Institute” which looks after the standards for products, services, processes, systems, and personnel. In easy way I should say that, they are the one which make rules about how a particular should work or use (RULEs can be broken sometimes ;)).

Now, What is meant by NULL?

NULL simply means unknown. Does unknown has any value?

NO!

NULL cannot be ZERO. It is simply NULL which is NOT DEFINED.

So ANSI has set some very basic standards for NULL. Any comparison or calculation performed with NULL is NULL. for example NULL + 1 = NULL.

If (NULL = ‘NULL’) => FALSE.

NULL is neither a string nor any number.

So, When I do SET ANSI_NULLS ON; I am simply telling SQL SERVER to follow the standard of ANSI. Let us see with below example.

When ANSI_NULLS is set to ON. ‘=’ operator is not able to identify NULL. This is the standard of ANSI that any comparison with NULL is always NULL. So you will never ever get a result for this. To find NULL in a column we use Keyword ‘IS NULL’.

Now let us check the same query by making ANSI_NULLS OFF.

 

So, here it is. Now it is no more following the ANSI standard.

Below is one more example snapshot. which makes it more clear.

In ideal situation, we should always follow the ANSI guidelines in our code.

 

 

2. SET NOCOUNT ON :

As the name suggest we are informing SQL Server to ‘Do Not Count’. But what exactly SQL Server should not count. Let us check below snapshot.

When I SET NO COUNT OFF, SSMS gave me details of how many rows are affected. At below snapshot there is not such details because we have SET NOCOUNT ON.

This can come very handy while improving the performance of Stored Procedure.

 

3. SET Quoted_Identifier ON

(Ref. –  https://msdn.microsoft.com/en-us/library/ms174393.aspx)

Consider a situation where you want your column name be ‘Identity’. But Identity is a keyword for SQL Server. How it will differentiate between a user referred Identitiy Or T-SQL referred Identity. This is where Quoted Identifier help us out. Below is in example taken from MSDN representing how we can use already present Keywords.

 

 

 

 

Above two Snapshots shows how with the help of Quoted Identifier I was able to use reserved Keyword of SQL Server.

 

MSDN has also explained how to use quoted identifier for Single and Double Quotation Marks. Please refer that for details.