Error "Msg 515, Level 16, State 2, Line 2.Cannot insert the value NULL into column 'Age', table 'tempdb.dbo.#t1_" column does not allow nulls. INSERT fails.

In this small blog post, I have something very interesting about temporary tables. So let me tell you how I got above titled error. Last week I have created a simple store procedure using temporary table on my office system and executed it. It was running fine.But when I ran it on my laptop it was giving error as below:

"Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'age', table 'tempdb.dbo.#T1_"  column does not allow nulls. INSERT fails.

I was surprised as it was working fine in Office. Next day when I reached to office I ran the same store procedure on my office system and again it was working fine. Now I was having clear idea that it is happening because of some connection problem. Without wasting a second I opened my laptop and connected to SSMS and without any confusion I just clicked on Tools> Options>Query Execution> Sql Server> ANSI. 

Please do not assume that I'm so intelligent as how quickly I found the issue. Actually what really happened is that two days back I was playing with query execution Options SET ANSI_NULL_DFLT_ON and that time I unchecked it and forgot to check it again. That is why I got clear idea where the problem is.

As per MSDN:
 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.

 As in my office system it was set to ON so Null value gets inserted in temporary table.
 But in my laptop it was set to OFF and when temp table created it was not null By default
 so it was not able to insert null Value in it.

Lets create a sample scenario to explain the same:  I will write a sample TSQL code to explain it.
Use master
GO
Create database TestVK
Go
Use TestVK
Go
Create Table #T1(id int, name char(5) , age int);
Go
Insert into #T1 values(1,'Arman',null)
Go
select * from #T1

GO
It will be inserted successfully.

Lets check the Null-ability status of this:
SELECT V.TABLE_NAME, V.COLUMN_NAME, V.IS_NULLABLE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS AS V
WHERE V.TABLE_NAME LIKE '#T1%';
GO

From above screen shot it is clear that all column are nullable.

Now close you query window and Go to Tools> Options>Query Execution> Sql Server> ANSI and unchecked the ANSI_NULL_DFLT_ON.

Now open a new query and execute below query and see the output:

Use TestVK;
Create Table #T1(id int, name char(5) , age int);
Go
Insert into #T1 values(1,'Zoya',null)
Go

OUTPUT:

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

Its because ANSI_NULL_DFLT_ON is OFF and the column for this table is set to not null. you can see the status of column by:

SELECT V.TABLE_NAME, V.COLUMN_NAME, V.IS_NULLABLE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS AS V
WHERE V.TABLE_NAME LIKE '#T1%';
GO
Output:

So next time when you are using temporary table in your procedure take care of this before going into big trouble. The precaution you can take is that Always define the Null-ability when defining tables – temporary or others.

 Thanks For Reading this Blog!!!