SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Null vs ''


Null vs ''

Author
Message
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8112 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5430 Visits: 4076
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;-)
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1228 Visits: 931
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
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1721
To NULL or not to NULL, that is the question... :-P

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.

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search