SnowConvert AI - Transact SQL - ANSI_NULLS

Applies to

  • SQL Server

  • Azure Synapse Analytics

Description

This statement specifies the ISO-compliant behavior of the Equals and Not Equal to comparison operators when used with null values in SQLServer. Please visit SET ANSI_NULLS to get more information about this statement.

Transact-SQL Syntax

 SET ANSI_NULLS { ON | OFF }
Copy

Sample Source Patterns

SET ANSI_NULLS ON

“SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.” (SQLServer ANSI_NULLS article).

Snowflake does not support this statement, so in the case of ANSI_NULLS ON, this is marked with an FDM (SSC-FDM-TS0027) because it does not have relevance in executing equal and not equal comparison operations. Here, you can find an explanation of the NULL treatment in Snowflake.

SQL Server

 SET ANSI_NULLS ON;
Copy

Snowflake

 ----** SSC-FDM-TS0027 - SET ANSI_NULLS ON STATEMENT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE **
--SET ANSI_NULLS ON
Copy

SET ANSI_NULLS OFF

“When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO 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 non-NULL values in the column”. (SQLServer ANSI_NULLS article).

In the case of the ANSI_NULLS OFF statement, this one is marked with an EWI (SSC-EWI-0040) because it requires extra manual effort.

SQL Server

 SET ANSI_NULLS OFF;
Copy

Snowflake

 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
SET ANSI_NULLS OFF;
Copy
Language: English