SET ANSI_NULLS option and null value comparison

Highly recommended: Set option Ansi_nulls to ON, use is null when comparing with null value or is NOT NULL

View and set values for this option

SETAnsi_nulls on--SET ansi_nulls OFFDECLARE @ANSI_NULLS VARCHAR(3)= 'OFF';IF( ( + & @ @OPTIONS)=  + )     SET @ANSI_NULLS = ' on';SELECT @ANSI_NULLS  as [ansi_nulls Option];

When SET ansi_nulls are on, a SELECT statement that uses WHERECOLUMN_NAME = null returns zero rows even if there is null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there is nonnull value s in column_name.

When SET ansi_nulls are OFF, the Equals (=) and not Equal to (<>) comparison operators does not follow the ISO standard .A SELECT statement that uses WHERECOLUMN_NAME =NULL returns the rows that has null values incolumn_name.A SELECT statement that uses WHEREcolumn_name <> null returns the rows that has nonnull values in The column. column_name &lt;&gt; xyz_value's SELECT statement returns all non-xyz_value is also not a NULL row. ">also, a SELECT statement that uses WHERE column_name <> xyz_ Value returns all rows that is not xyz_value and that is not NULL.

when SET ansi_nulls is on, all comparisons against a null value evaluate to UNKNOWN. when SET ansi_nulls is OFF, comparisons of any data against a null value evaluate TRUE if the data value is null. if SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option in the current database applies.

for a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ansi_nulls, use I S null and is not NULL in comparisons that might contain null values.

SET Ansi_nulls must also is on if you were creating or changing indexes on computed columns or indexed views.If SET Ansi_nulls is OFF, no CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or Indexed views would fail.also, when you execute a SELECT statement, if SET ansi_nulls is OFF, SQL Server would ignore the index values on computed Columns or views and resolve the select operation as if there were no such indexes on the tables or views.

ANSI_NULLS is one of the seven set options that must are set to required values when dealing with indexes on computed Colum NS or indexed views. The options ansi_padding, Ansi_warnings, ARITHABORT, QUOTED_IDENTIFIER, and Concat_null_yields_null must also be set T O on, and Numeric_roundabort must is set to OFF.

When ANSI_NULLS is on, following the standard of SQL92, only is null can be used to determine if the value is null, but not = or <> to be compared with NULL, any value including null value and null value do = or <> Operations are given false.

When ANSI_NULLS is off, the SQL92 standard is no longer followed, and you can use = and <> to do a bool operation with NULL.

Example:

if object_id('dt_test') is  not NULL    Drop Tabledbo.dt_testCreate Tabledbo.dt_test (IDint)Insert  intodbo.dt_testValues(1),(NULL)--When SET ANSI_NULLS off,null and null are the same, null and non-null values are not equal,SETAnsi_nullsOFFSelect * fromdbo.dt_testwhereId=NULL--returns False when SET ANSI_NULLS On,null and any value (including null) are compared--You must use is NULL to determine whether null or is isn't null to determine non-nullSETAnsi_nulls onSelect * fromdbo.dt_testwhereId=NULLSelect * fromdbo.dt_testwhereId is NULL

About the extension of NULL:

1. Aggregate function count, only select COUNT (*) and select COUNT (1) will be counted as null rows, and select COUNT (ID) ignores rows with the ID column null

2. Other aggregate functions except count, calculated without considering null rows

3. Check CONSTRAINT, null rows are not restricted by CHEKC CONSTRAINT, for example, if a check CONSTRAINT condition is c1>, in the case where the column is allowed to be null 10, inserting a null value on the C1 column or updating to NULL does not violate the check CONSTRAINT.

4. When group BY, all null is grouped

Reference article

Http://www.cnblogs.com/TeyGao/p/3521657.html

Https://msdn.microsoft.com/zh-cn/library/ms188048.aspx

SET ANSI_NULLS option and null value comparison