When the SQL Server 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:

  • 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:

    SET ANSI_NULLS OFF