SQL Server: NULL As Nothing vs NULL As String Value in SSMS

While loading data from Flat Files, I have found that few column values are inserted as ‘NULL’ and client’s application crashed. WHY ?.  Because ‘NULL’ as nothing and NULL as character value are two different things. I found one more blessing of SQL Server 2008. That in SQL Server Management Studio 2008, it shows NULL values in result set with different color. How it helps us in real life, let’s find it with a simple query which we will execute on both SQL Server 2008 and SQL Server 2005.

USE AdventureWorks
GO
SELECT  DocumentID,
Title,
DocumentSummary
FROM    Production.Document

Here is result set from SQL Server 2008

And following result set is from SQL Serve 2005.

In SQL Server 2005, from result set, you can’t say which rows contains NULL (as nothing) values and which contains ‘NULL’ (as character). Lets update one of row in above table on both versions.

USE AdventureWorks
GO
UPDATE Production.Document
SET DocumentSummary = 'NULL'
WHERE DocumentID = 9

Execute select query on both sides and have a look on result-set. In SQL Server 2005, you can’t differentiate which NULL indicates nothing and which NULL are character values, but in SQL Server 2008 you can easily identify such data.

 

I have learned a lesson from this incident and now,to avoid such discrepancies, I like to execute following script to avoid both, NULL as character value problem and extra spaces problem, once I have loaded data in my target tables.

DECLARE @SqlText VARCHAR(1000)
DECLARE MY_CURSOR Cursor FAST_FORWARD
FOR SELECT  '[' + sc.name + '].[' + tb.name + ']' AS TableName,
cl.name AS ColumnName
FROM    sys.columns cl
INNER JOIN sys.tables tb ON cl.object_id = tb.object_id
INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
WHERE   tb.type = 'U'
AND cl.system_type_id IN ( 167, 175, 231, 239 )
AND cl.is_computed = 0
AND sc.principal_id = 1
ORDER BY TableName
/*
varchar = 167
nvarchar= 231
char = 175
nchar = 239
*/
OPEN My_Cursor
DECLARE @TableName VARCHAR(500),
@ColumnName VARCHAR(500)
Fetch NEXT FROM MY_Cursor INTO @TableName, @ColumnName
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT  @SqlText = 'UPDATE ' + @TableName + ' SET [' + @ColumnName
+ '] = CASE LTRIM(RTRIM([' + @ColumnName
+ ']))
WHEN ''NULL'' THEN NULL ELSE LTRIM(RTRIM(['
+ @ColumnName + '])) END'
EXEC ( @SqlText )                                                                      
FETCH NEXT FROM MY_CURSOR INTO @TableName, @ColumnName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR