Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Null vs ''


Null vs ''

Author
Message
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 6694
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)

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."
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 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
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 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-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 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