When the driver establishes a connection, the driver sets the Microsoft SQL Server database option ANSI_NULLS to on. This action ensures that the driver is compliant with the ANSI SQL standard, which makes developing cross-database applications easier.
By default, Microsoft SQL Server does not evaluate null values in SQL equality (=) or inequality (<>) comparisons or aggregate functions in an ANSI SQL-compliant manner. For example, the ANSI SQL specification defines that col1=null as shown in the following Select statement always evaluates to false:
SELECT * FROM table WHERE col1 = NULL
Using the default database setting (ANSI_NULLS=off, the same comparison evaluates to true instead of false.
Setting ANSI_NULLS to on changes how the database handles null values and forces the use of IS NULL instead of =NULL. For example, if the value of col1 in the following Select statement is null, the comparison evaluates to true:
SELECT * FROM table WHERE col1 IS NULL
In your application, you can restore the default Microsoft SQL Server behavior for a connection in the following ways:
Note: Setting ANSI_NULLS to off is not supported for Microsoft Azure Synapse Analytics or Microsoft Analytics Platform System.
Use the InitializationString property to specify the SQL command set ANSI_NULLS off. For example, the following URL ensures that the handling of null values is restored to the Microsoft SQL Server default for the current connection:
jdbc:datadirect:sqlserver://server1:1433;InitializationString=
set ANSI_NULLS off;DatabaseName=test
Explicitly execute the following statement after the connection is established:
Use the InitializationString property to specify the SQL command set ANSI_NULLS off. For example, the following URL ensures that the handling of null values is restored to the Microsoft SQL Server default for the current connection: