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.
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
nice information
ReplyDelete