#0327 – SQL Server – Fun with temporary tables – Impact of ANSI_NULL_DFLT_ON

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:



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;
IF (@@OPTIONS & 1024) > 0

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

--Table definition 
--Notice that the NULL-ability on the columns is undefined
                          [City] VARCHAR(50),
                          [State] VARCHAR(50)

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

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

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
WHERE tisc.TABLE_NAME LIKE '#NullsCheck%';



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.


4 thoughts on “#0327 – SQL Server – Fun with temporary tables – Impact of ANSI_NULL_DFLT_ON

  1. Jeff Moden

    As always, good and to-the-point article, Nakul. Thank you for what you do and for helping others out.

    You wrote:
    1. Always define the NULL-ability when defining tables – temporary or otherwise

    I absolutely agree and you already know this but I’d like to clarify that a bit for the casual reader…

    This doesn’t mean that you need to necessarily create tables (temporary or otherwise) before you use the tables. If you’re a newbie reading this, that might sound incredibly confusing at first until you realize that there is such a thing as the SELECT statement being able to create and populate a table on the fly using the INTO clause (commonly referred to as a “SELECT/INTO”). This is a very powerful and high performance tool used to rapidly create temporary and other tables on the fly. It not only creates and populates a new table on the fly, but it also copies certain column attributes from the source such as datatype, the IDENTITY property, and NULLability, which means that there is no need to explicity delare the NULLability during such operations and you don’t necessarily need to precreate the target table. There are also methods to change the the properties of the newly formed columns during the SELECT/INTO (a whole article could be written on that subject, alone).

    Shifting gears back to the article, great job, Nakul. Keep up the good work.


  2. Nakul Vachhrajani


    Thank-you, Jeff for taking the time out to read my post and sharing your kind views! Also, thank-you for your detailed comments related to the behaviour of the bulk operation SELECT…INTO.

    Thank-you for the encouraging and kind words! I try my best to share my learnings and experiences from around me so that it may be of help to all who visit the site.


  3. Pingback: #0376 – SQL Server – Msg 2714: There is already an object named ‘#tableName’ in the database. | SQLTwins by Nakul Vachhrajani

  4. Pingback: #0385 – SQL Server – Query Development and NULL values | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s