Wednesday, 29 May 2013

SET ANSI_NULLS in sql server

SET ANSI_NULLS in sql server

Problem ::

                  some time we are try to select record from table where column is = null or column is not equal to null (<>) but we get zero records but in table a record is exist where column is = null or col -umn is not equal to null (<>),becouse somewhere in the query or proc we can add this statement
" SET ANSI_NULLS ON"
Click here to know about set-quotedidentifier-in-sql

Discription::

                                     When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_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 that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value
and that are not NULL.

Check the following query for better understanding.


Create table #Temp(id int,Names Varchar(100))
Insert into #Temp(id,Names)values(1,'VITA')
Insert into #Temp(id,Names)values (2,NULL)
Insert into #Temp(id,Names)values (3,'ADITYA')
Insert into #Temp(id,Names)values (4,'PRANAV')
Insert into #Temp(id,Names)values (5,NULL)
Insert into #Temp(id,Names)values (6,'SANDIP')
Insert into #Temp(id,Names)values (7,'GAIKWAD')

Check the effect of "SET ANSI_NULLS OFF" on following query


SET ANSI_NULLS OFF
select * from #Temp where Names =NULL

Check the effect of "SET ANSI_NULLS ON" on following query


SET ANSI_NULLS ON
select * from #Temp where Names =NULL
select * from #Temp where Names =is NULL

1 comment:

8451831077