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.
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.
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.
cl.name AS ColumnNameFROM sys.columns clWHERE tb.type = 'U'AND cl.is_computed = 0AND sc.principal_id = 1/*varchar = 167nvarchar= 231char = 175nchar = 239*/OPEN My_Cursor@ColumnName VARCHAR(500)WHILE ( @@FETCH_STATUS <> -1 )+ '] = CASE LTRIM(RTRIM([' + @ColumnName+ ']))+ @ColumnName + '])) END'EXEC ( @SqlText )CLOSE MY_CURSORDEALLOCATE MY_CURSOR