skip to main content
DataDirect Connect Drivers : Microsoft SQL Server Driver : Null Values
  
Null Values
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