Null values are being inserted in columns not allowing Null values

Question

I am using SQL Server 2008. 
I have a table which has columns of nvarchar type and allow null set to false.

I notice that there are records inserted which have null values in these columns.

Any suggestions how I can prevent this from happening? 

Answer 1

Could you please share the script of the table?

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 2

Hi Mahesh,

Are you sure those are NULL values instead of 'NULL' strings? There is a lot of difference between them. Try this for yourself.

use tempdb
go
create table dbo.nulltest (c1 int not null, c2 nvarchar(50) not null)
go
--insert will fail
insert dbo.nulltest
	select 1, null
go
/*
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'c2', table 'tempdb.dbo.nulltest'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/

--insert will run
insert dbo.nulltest
	select 1, 'null'
go

select * from dbo.nulltest
/*
1	null
*/

--see if you get any records that have null in them
select * from dbo.nulltest where len(ltrim(rtrim(c2))) > 0 and c2 like 'null%'

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 3

SQL Server will not allow NULL values to be inserted into columns that do not allow NULL.  My guess is that the data is question is not actually NULL but rather a string with the value "NULL".  This can be confusing when viewing results.  For example:

 

DROP TABLE dbo.NullExample;
CREATE TABLE dbo.NullExample(
	NonNullColumn char(4) NOT NULL
	,NullColumn char(4) NULL
	);
GO
INSERT INTO dbo.NullExample VALUES('NULL', NULL);
GO
SELECT NonNullColumn, NullColumn FROM dbo.NullExample;
GO

 

SSMS will show both values as "NULL" but the real NULL value will show a yellow background with results in grid mode. 

To prevent this issue in parameterized queries from application code , specify DBNull.Value as the value instead of the string "NULL".

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/