This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a small note on QUOTED_IDENTIFIER and ANSI_NULL of SQL Server.
This is very basic and most popular topic for all SQL Server Professionals.
You can find an N number of article and discussion on this subject. But I always like writing about the popular topic in my words.
QUOTED_IDENTIFIER ON/OFF:
|
1 2 3 4 |
SET QUOTED_IDENTIFIER ON GO SET QUOTED_IDENTIFIER OFF GO |
This option is ON and characters enclosed within double quotes then is treated as an Identifier.
This option is OFF and characters enclosed within double quotes then is treated as a Literal.
Using this option you can use SQL Server reserved words as an Identifiers.
For example:
|
1 2 3 |
SET QUOTED_IDENTIFIER ON GO CREATE TABLE dbo.”Table” (id int,”View” VARCHAR(255)) GO |
ANSI_NULL ON/OFF:
|
1 2 3 4 |
SET ANSI_NULLS ON GO SET ANSI_NULLS OFF GO |
This option basically for ANSI NULL comparisons. If this option ON and you compare your query value with a NULL, the result is 0.
If this option OFF and you compare your query value with a NULL, then the result is NULL.
In a future version of SQL Server, ANSI_NULLS will always be ON, and any applications that explicitly set the option to OFF will generate an error.
