When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these - QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.
QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL (Last 2 Definition)
pinaldave
Can u please provide any example of both of these, So that more clearly we can understand these.
Got it… I have Created a table with fields Id and Name then i inserted many values in that table and also inserted a NULL value. in the Name field
Then When i am rnning below querry it not returns any record to me.
SET ANSI_NULLS ON
SELECT Name FROM MyTable(Nolock) WHERE Name=NULL
AND
When iam running below querry it returns the row with NULL value
SET ANSI_NULLS OFF
SELECT Name FROM MyTable(Nolock) WHERE Name=NULL
So, If in case of null when ANSI_NULLS is on Wht are the benefits of this i think this is wrong caz it does not return any row which having null. Why this is present in SQL any Specific reasons, Where we use this.????
I’m a SQL beginner, and this stuff drives me crazy. However, we’ve using “is null” instead of “= null” and it seems to work regardless of the setting. Try this. This shows the difference between the two approaches:
set ansi_nulls on
declare @myTab table (id int null, [name] varchar(10) null)
insert into @myTab (id,[name]) values (1, ’sam’)
insert into @myTab (id,[name]) values (2, ‘dave’)
insert into @myTab (id,[name]) values (3, null)
select * from @myTab
select * from @myTab where [name] is null
select * from @myTab where [name] = null
Hi
in a query set ansi_nulls on
and select the null coulmn is gives null value
if set ansi_nulls off also behave same.
then how to know diff between the ansi_nulls on and off
do mail to me.
Thank you
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. (FALSE)
When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL
When SET ANSI_NULLS is ON,
a SELECT statement using WHERE Emp_Name= NULL returns zero rows even if there are null values in Emp_Name.
A SELECT statement using WHERE Emp_Name NULL returns zero rows even if there are nonnull values in Emp_Name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To () comparison operators do not follow the SQL-92 standard.
A SELECT statement using WHERE Emp_Name= NULL returns the rows with null values Emp_Name.
A SELECT statement using WHERE Emp_Name NULL returns the rows with nonnull values in the column.
SET ANSI_NULLS also must be ON for executing distributed queries and when creating or manipulating indexes on computed columns or indexed views.
If SET ANSI_NULLS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error listing all SET options violating the required values.
When executing a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select as though there were no such indexes on the tables or views.
If you want a script to work regardless of the ANSI NULSS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values.