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 AdventureWorksGOSELECT DocumentID,Title,DocumentSummaryFROM 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 AdventureWorksGOUPDATE Production.DocumentSET 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_FORWARDFOR SELECT '[' + sc.name + '].[' + tb.name + ']' AS TableName,cl.name AS ColumnNameFROM sys.columns clINNER JOIN sys.tables tb ON cl.object_id = tb.object_idINNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_idWHERE tb.type = 'U'AND cl.system_type_id IN ( 167, 175, 231, 239 )AND cl.is_computed = 0AND sc.principal_id = 1ORDER BY TableName/*
varchar = 167nvarchar= 231char = 175nchar = 239*/OPEN My_CursorDECLARE @TableName VARCHAR(500),@ColumnName VARCHAR(500)Fetch NEXT FROM MY_Cursor INTO @TableName, @ColumnNameWHILE ( @@FETCH_STATUS <> -1 )BEGINSELECT @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, @ColumnNameENDCLOSE MY_CURSORDEALLOCATE MY_CURSOR
Thats what happened with me, last week ;)
ReplyDeleteThe same with me also nicely point-of-view!!!
ReplyDeleteThanks for sharing this secret man , we are also thinking to migrate from SQL Server 2005 to SQL Server 2008 and this is something which will definitely helps us.
ReplyDeleteJavin
10 basic mysql commands to remember