INF: Differences in Column Nullability

ID: Q138761

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5
Microsoft Open Database 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

which clients can use to change their default to the ANSI standard. Since the ODBC standard is based on the ANSI standard, a 2.50.0121 or later Microsoft SQL Server ODBC Driver sets this option on when it detects it is running against a 6.0 or later SQL Server. DB-Lib clients, however, are more targeted to maintaining compatibility with older SQL Servers, and they do not set this option. If a procedure like the following is created through a DB-Lib application, such as ISQL/w or the SQL Enterprise Manager:


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

then the temporary table #testit will be created with the columns not allowing nulls. This fact will be recorded in the stored procedure. When the procedure is later executed by an ODBC client, however, the temporary table created for the ODBC client will allow nulls in both columns. This conflict between what is recorded in the stored procedure and the current version of the table can prevent the proper operation of the stored procedure. All problems will be prevented if the procedure is changed to explicitly state NULL or NOT NULL for each column:


   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

The same type of problems could also occur if the first procedure were created from an ODBC application and later executed by a DB-Lib application.

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

and then an ODBC application does:


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

Microsoft ODBC drivers will change the create table statement to:


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

before sending it to the server in order to enforce the ANSI standard.

Additional query words: sql6 dblib odbc

Keywords : kbinterop kbprg kbusage SSrvDB_Lib SSrvProg
Version : 2.5.0121 6.0 6.5
Platform : WINDOWS
Issue type :