When
SET ANSI_NULLS ON
it means ISO Standard is being followed.
means
=
and
<>
should not be used for null comparison.
If you want to use =
or <>
for null comparison use
SET ANSI_NULLS OFF
it means do not follow ISO Standard.
SET ANSI_NULLS
should be set to ON for executing distributed queries
for maintaining compatibility of queries accross Servers.
and
QUOTED_IDENTIFIER
It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes. When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a literal.