Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Null vs '' Expand / Collapse
Author
Message
Posted Thursday, January 03, 2013 10:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 1,746, Visits: 2,553
If you're providing data for report or any type of BI application, remove NULLs entirely. NULLs cause serious and severe logic considerations that most people don't fully understand (including even some "DBAs").

If ANY user, even a power user, needs to write queries against the data, NULLs should especially be avoided and will cause you far more headaches than they are worth.

Replace any NULLs either with blank or 'Unknown' or equivalent that is clear to the end-users and that they support.


SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1402474
Posted Thursday, January 03, 2013 10:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
ScottPletcher (1/3/2013)
If you're providing data for report or any type of BI application, remove NULLs entirely. NULLs cause serious and severe logic considerations that most people don't fully understand (including even some "DBAs").

If ANY user, even a power user, needs to write queries against the data, NULLs should especially be avoided and will cause you far more headaches than they are worth.

Replace any NULLs either with blank or 'Unknown' or equivalent that is clear to the end-users and that they support.
i will second Scott here.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1402682
Posted Friday, January 04, 2013 3:02 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 9:59 AM
Points: 692, Visits: 754
I think I said somewhere before - it is not for reporting / BI. Where I have done data warehouses before I have left the data as is.
Although I would counter that and say that there will always be a case for checking data and reviewing if there is a cleansing / modifiying requirement. Although generally I would retain the original column in the table and add a new column with the modified data. This retaining the opriginal 'as is' and also the cleansed value.

The data in this case is used for comparisons and number crunching. Therefore on some fields there could be an advantage if the data is prepped first. Was just wondering if going to NULL was the sensible choice.

Cheers
M
Post #1402762
Posted Friday, January 04, 2013 6:09 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
To NULL or not to NULL, that is the question...

Whatever you choose, I'd do something like this:


--Get rid of any leading, trailing, or single spaces then if blank make it NULL
SELECT NULLIF(LTRIM(RTRIM(foo)),'') FROM bar

--Or make all NULLs blank
SELECT ISNULL(foo,'') FROM bar

--Or make all NULLs some default value (in this case zero)
SELECT ISNULL(foo,0) FROM bar

--Or a combination of all three
SELECT ISNULL(NULLIF(LTRIM(RTRIM(foo)),''),0) FROM bar


I agree generally with the concept that NULL means "unknown" and blank means blank. Even better is to have a design in which NULL is not an option and there is always some default that is not null (or blank). In the real world, especially when importing and scrubbing data, the purity of db design and development sometimes gets lost in the weeds.

I have to deal often with an application that CANNOT pass NULLs at all. So for this particular app, all of my input parameters--even numeric values, xml, and uniqueidentifiers--have to be char/varchar because the application passes '' (blank) rather than null. You can imagine the validation headaches that causes!

Another possible reason to use blank values might if they are being concatenated with other values. If any of the values are NULL then the result of the concatenation will also be NULL. Thus any NULLs would have to be converted to a string value anyway. It all depends on where you want to do the work--fixing up the raw data or just doing what needs to be done in the query procedure at run time.

 
Post #1403175
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse