Category Archives: Differences

SET ANSI_NULLS ON/OFF Setting in Sql Server

Differences, Sql Server, , ,

While creating Stored Procedures, User Defined Functions etc, most of us use the SET ANSI_NULLS ON/OFF and SET QUOTED_IDENTIFIER ON/OFF Settings. In this article will discuss on the SET ANSI_NULLS  { ON | OFF } Setting.

To understand this with an example, let us create table Name and insert three records in this table as below:

CREATE TABLE dbo.Name(FirstName VARCHAR(50),LastName Varchar(50)) GO 
INSERT INTO dbo.Name VALUES('BASAVARAJ','BIRADAR'),
 ('KALPANA','PATIL'),
 ('MONTY', NULL) GO

Depending on the ANSI_NULLS setting value either ON or OFF, SQL Server behaves differently while comparing with NULL value.

SET ANSI_NULLS  ON

When this setting value is ON (i.e. SET ANSI_NULLS ON) then comparison with NULL value using = and <> comparison operator will return false. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL
Result:
FirstName               LastName 
----------------------- --------------------------
(0 row(s) affected)
FirstName               LastName
----------------------- --------------------------
(0 row(s) affected)

So, when this setting value is ON we need to use IS NULL or IS NOT NULL instead of comparison operator = and <>. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NOT NULL 
Result:
FirstName                LastName 
------------------------ ------------------------ 
MONTY                    NULL 
(1 row(s) affected) 
FirstName                LastName 
------------------------ ------------------------ 
BASAVARAJ                BIRADAR 
KALPANA                  PATIL 
(2 row(s) affected)

 

SET ANSI_NULLS  OFF

On the other hand if this setting value is OFF (i.e. SET ANSI_NULLS OFF) then comparison with NULL value using = and <> comparison operator returns TRUE if the value to be compared is NULL and NON NULL value respectively. Below Script demonstrates this fact.

SET ANSI_NULLS OFF 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL 
Result:
FirstName               LastName 
----------------------- --------------------- 
MONTY                   NULL 
(1 row(s) affected) 
FirstName               LastName 
----------------------- --------------------- 
BASAVARAJ               BIRADAR 
KALPANA                 PATIL 
(2 row(s) affected)

 

Another important point to note is that, the SET ANSI_NULLS { ON| OFF } setting with which we create Stored Procedure/UDF etc will be stored in the meta data. So, whenever Stored Procedure/UDF executes, it will use these setting stored in the meta data. It will ignore the settings of the client or the calling application.

 Below query can be used to find the objects which are created with SET ANSI_NULLS setting as OFF.

SELECT OBJECT_NAME (object_id) FROM sys.sql_modules
WHERE uses_ansi_nulls = 0 -- 0 means OFF and 1 means ON

As per BOL for SQL Server 2008 or 2012, in future versions this setting value will always be ON and explicitly setting it to OFF will result in error. So, it is better avoid explicitly setting  this in future development work.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version

Difference Between Sql Server VARCHAR and NVARCHAR Data Type

Differences, Sql Server, , , ,

Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:

Varchar[(n)] NVarchar[(n)]
Basic Definition Non-Unicode Variable Length character data type.
Example:
DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’
SELECT @FirstName 		UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters.
Example:
DECLARE @FirstName AS NVARCHAR(50)= ‘BASAVARAJ’
SELECT @FirstName
No. of Bytes required for each character It takes 1 byte per character

Example:
DECLARE @FirstName AS VARCHAR(50) = ‘BASAVARAJ’
SELECT @FirstName AS FirstName,
DATALENGTH(@FirstName) AS
Length

Result:
FirstName Length
BASAVARAJ 9

It takes 2 bytes per Unicode/Non-Unicode character.
Example:
DECLARE @FirstName AS NVARCHAR(50)= ‘BASAVARAJ’
SELECT @FirstName AS FirstName,
DATALENGTH(@FirstName) AS Length

Result:
FirstName Length
BASAVARAJ 18
Optional Parameter n range Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters. Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters
If Optional Parameter n is not specified in the variable declaration or column definition If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.
Example:
DECLARE @firstName VARCHAR = ‘BASAVARAJ’
SELECT
@firstName FirstName, DATALENGTH(@firstName) Length

Result:
FirstName Length
B 1		 If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.
Example:
DECLARE @firstName NVARCHAR = ‘BASAVARAJ’
SELECT
@firstName FirstName, DATALENGTH(@firstName) Length

Result:
FirstName Length
B 2
If Optional Parameter n is not
specified in while using
CAST/ CONVERT functions		 When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName VARCHAR(35) =
‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’

SELECT CAST(@firstName AS VARCHAR) FirstName,
DATALENGTH(CAST(@firstName AS VARCHAR)) Length

Result:
FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 30		 When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName NVARCHAR(35) =
‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’

SELECT CAST(@firstName AS NVARCHAR) FirstName,
DATALENGTH(CAST(@firstName AS NVARCHAR)) Length

Result:
FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 60
Which one to use? If we know that data to be stored in the column or variable doesn’t have any Unicode characters. If we know that the data to be stored in the column or variable can have Unicode characters.
Storage Size Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset. Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

As both of these are variable length datatypes, so irrespective of the length (i.e. optional parameter n value) defined in the variable declaration/column definition it will always take the no. of bytes required for the actual charcters stored. The value of n defines maximum no. of characters that can be stored.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG

Please correct me, if my understanding is wrong. Comments are always welcome

Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Differences, Sql Server, , , , , , ,

In this article we will discuss on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF. Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. As a better practice we should always use SET QUOTED_IDENTIFIERS ON setting.

SET QUOTED_IDENTIFIER ON SET QUOTED_IDENTIFIER OFF
Characters Enclosed within double quotes is treated as Identifier is treated as Literal
Try using Characters Enclosed within double quotes as identifier Works
Example: Below statement to create a table with table name “Table” succeeds.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO
 Fails
Example: Below statement to create a table with table name “Table” Fails.

SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO

Error Message:
Msg 102, Level 15, State 1,
Line 1 Incorrect syntax near ‘Table’.
Try using Characters Enclosed within double quotes as Literal. Fails
Example: Below statement fails.

SET QUOTED_IDENTIFIER ON
GO
SELECT "BIRADAR"

Error Message:
Msg 207, Level 16, State 1,
Line 1 Invalid column name ‘BIRADAR’.

 Works
Example: Below Statement Works.

SET QUOTED_IDENTIFIER OFF
GO
SELECT "BIRADAR"
Characters Enclosed within single quotes is treated as Literal
Example:

SET QUOTED_IDENTIFIER ON
GO
SELECT 'BIRADAR'
 is treated as Literal
Example:

SET QUOTED_IDENTIFIER OFF
GO
SELECT 'BIRADAR'
How to find all the objects which are created with SET QUTOED IDENTIFIER ON/OFF Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as ON:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules 
WHERE uses_quoted_identifier = 1
 Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as OFF:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules
WHERE uses_quoted_identifier = 0
Which One to USE? Best practice is to use the SET QUOTED_IDENTIFIER ON setting. Try to avoid using SET QUOTED_IDENTIFIER OFF setting. As more and more new features require this setting value to be ON. For example while working with Filtered Indexes SET QUOTED_IDENTIFIER setting should be ON otherwise we will get into an exception as demonstrated in the article INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ ….

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

Please correct me, if my understanding is wrong. Comments are always welcome.