ANSI_NULLS

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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 }

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;

Snowflake

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

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;

Snowflake

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

Related EWIs

1. SSC-EWI-0040: The statement is not supported in Snowflake

2. SSC-FDM-0027: SET ANSI_NULLS ON statement may have different behavior in Snowflake