Some lessons are learnt the hard way. Today, it is a well-accepted best practice to always define the NULL-ability on the columns of a temporary table definition. This is because the ANSI_NULL_DFLT_ON connection option influences the default value being inserted into a column when the NULL-ability of the column is not specified. Here’s the extract from Books-On-Line:

When SET ANSI_NULL_DFLT_ON is ON, new columns created by using the ALTER TABLE and CREATE TABLE statements allow null values if the nullability status of the column is not explicitly specified. SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

Most enterprise applications use temporary objects, especially table variables and temporary tables. Some of these applications have been around since a decade or more. They have a mix of “legacy” code (written in the days of SQL 2000) and “modern” code (written in SQL 2008 and beyond).

I was recently called upon to assist with an error that one of our customers was encountering in their production environment. They were attempting to run a weekly routine and encountered an error similar to the following:

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ‘City’, table ‘tempdb.dbo.#NullsCheck_________________________________________________________________________________________________________000000000008’; column does not allow nulls. INSERT fails.
The statement has been terminated.

As is the case with most production issues, everything was working fine in our development and quality assurance environments.

While we were going through the scripts to see what could have gone wrong, we learnt that in another troubleshooting attempt by the technical support team, the same routine executed successfully when executed from another SQL Server client. The focus immediately shifted to the connection options.

Once we started reviewing the connection options, the problem was detected almost immediately. In the SSMS on the SQL server client environment where errors were encountered, the ANSI_NULL_DLFT_ON was set to OFF/unchecked in Tools –> Options –> Query Execution –> SQL Server –> ANSI:

ANSI_NULL_DFLT_ON is ON

On most SQL Server clients, the ANSI_NULL_DFLT_ON is set to ON by default. In such environments, when an attempt is made to insert NULL values into a column where the NULL-ability is undefined, no error will be reported. The following example demonstrates this.

USE AdventureWorks2012;
GO
IF (@@OPTIONS & 1024) > 0
    PRINT 'ANSI_NULL_DFLT_ON is SET';
ELSE
    PRINT 'ANSI_NULL_DFLT_ON is OFF';
GO

--Safety Check
IF OBJECT_ID('tempdb..#NullsCheck','U') IS NOT NULL
    DROP TABLE #NullsCheck;
GO

--Table definition 
--Notice that the NULL-ability on the columns is undefined
CREATE TABLE #NullsCheck ([Id] INT IDENTITY(1,1),
                          [City] VARCHAR(50),
                          [State] VARCHAR(50)
                         );
GO

--Attempt to insert test data
INSERT INTO #NullsCheck ([City],[State])
VALUES (NULL, NULL),
       ('Portsmouth','New Hampshire'),
       ('Boston', 'Massachusetts');
GO

--Select from the table
SELECT * FROM #NullsCheck;
GO

/*****************************
RESULTS
---------------------------
Id  City        State
---------------------------
1   NULL        NULL
2   Portsmouth  New Hampshire
3   Boston      Massachusetts
*****************************/

Before we close the query editor window in SSMS, let us take a quick look at the NULL-ability on the columns of the temporary table.

--Check the column properties
SELECT tisc.TABLE_NAME,
       tisc.COLUMN_NAME,
       tisc.IS_NULLABLE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS AS tisc
WHERE tisc.TABLE_NAME LIKE '#NullsCheck%';
GO

 

ANSI_NULL_DFLT_ON is OFF

When ANSI_NULL_DFLT_ON is OFF (as it was in this case), the same script will return the error that we were seeing.

This is because, when the temporary table is created, the table definition has been created with the columns as NOT NULL. Here’s the same check as above, but after turning the ANSI_NULL_DFLT_ON to OFF:

Lessons Learnt

1. Always define the NULL-ability when defining tables – temporary or otherwise
   
2. Whenever a new coding standard is adopted for an existing system, it is always a good idea to have an inventory of objects that violated the coding standard when the standards were adopted
   
3. As service releases of the product are released, it is a good idea to revisit the inventory and make the entire code conform to the standard over time

Further Reading

• Fun with Temporary Tables – Named Constraints, Msg 2714, 1750 [Link]
  
• Fun with Temporary Tables – Foreign Keys [Link]
  
• ANSI_NULL_DFLT_ON [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.