INF: Differences in Column Nullability

• Microsoft SQL Server, versions 6.0 & 6.5
• Microsoft Open Data Base Connectivity, version 2.5

SUMMARY

The nullability of columns in SQL Server temporary tables referenced by stored procedures can affect the operation of stored procedures.

MORE INFORMATION

The execution plans built for SQL Server stored procedures contain information on the nullability (the property of allowing or not allowing nulls) of all the table columns referenced by the stored procedure. Proper execution of the stored procedure depends on the nullability information stored in the execution plan agreeing with the actual nullability of the tables being referenced.

If sites are running a mix of DB-Lib and ODBC clients executing stored procedures on SQL Server 6.0 or later, it is important that all temporary tables referenced by those stored procedures be created with NULL or NOT NULL explicitly stated for all of the columns. This is because ODBC and DB- Lib clients have different defaults regarding whether columns are NULL or NOT NULL if this is not explicitly stated.

Prior to version 6.0, SQL Server followed non-ANSI rules regarding nullability defaults. SQL Server 6.0 introduced the command:

   SET ANSI_NULL_DFLT_ON ON

   create procedure testproc as
   create table #testit (cola int, colb char(3))
   insert into #testit values (1, 'aaa')
   select * from #testit
   go

   create procedure testproc as
   create table #testit (cola int NOT NULL, colb char(3) NOT NULL)
   insert into #testit values (1, 'aaa')
   select * from #testit
   go

An example of the types of problems encountered is inherent in the fact that the nullability of the columns has been changed. If a procedure were created and tested with ODBC Test, the procedure would allow nulls to be added to the temporary table simply because the columns default to NULL. If the procedure were then executed from ISQL/w it would start generating errors if the user attempted to insert null data because the version of the temporary table created for the DB-Lib client would default to NOT NULL.

If a stored procedure references a temporary table that is created outside the procedure, this issue can also affect any version of the Microsoft ODBC driver running against SQL Server 4.21a or earlier servers. This is because if NULL or NOT NULL is not specified on any CREATE TABLE statement issued through the drivers, the drivers automatically add NULL to the column definition before passing the statement to the server. For example, if the following procedure is created in a 4.21a server using ISQL/w:

   create table #testit (cola int, colb char(3))
   go
   create procedure testproc as
   insert into #testit values (1, 'aaa')
   select * from #testit
   go

   SQLExecDirect(hstmt, "create table #testit
                         (cola int, colb char(3))", SQL_NTS);
   SQLExecDirect(hstmt, "execute testproc", SQL_NTS);

   create table #testit (cola int null, colb char(3) null)