Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be submitted as NULL and sometimes it will be an empty string.

In terms of the client, an empty string simply means a value was not entered.

Should I convert all the empty varchar parameters to NULL before inserting the data?

I can't decide if this falls into the realm of a best-practice to preserve the integrity of the data or a bad idea because I'm not representing what was actually submitted to the database.

I should note that all inserts and updates are tracked in an audit log.

share|improve this question
2  
Converting all empty strings to NULL would increase your Oracle compatibility because that's what Oracle has been doing since ages. Most people dislike this behaviour –  a_horse_with_no_name Jul 20 '11 at 17:11
add comment

4 Answers

up vote 7 down vote accepted

As a DBA, your sacred duty is to take whatever data was entrusted to you, and leave it as is.

A NULL in a field usually means "DATA was not entered" or "DATA is not applicable" (but can have other meanings - it is context specific), and may actually MEAN something to the business layer. An empty string may actually be what the user entered and WANTS. Of course, I am generalizing - possibly the reason you get either NULLs or '' (empty strings) is due to some sloppy coding in the business layer (let us hope not, though).

Either way, have a chat with whoever provides the Middle Tier and see what they are using NULL vs Empty String for - and then go from there. Maybe they are thinking that they always pass in an Empty String (or always NULLS) and are not aware of the randomness (in which case advise them on what you think should be the standard), or maybe NULL really is information to them

share|improve this answer
    
@MartinSStoller: do you a think it would be better to block these empty strings through an error message? In the context of this particular data, an empty string will not have meaning to the user. –  8kb Jul 20 '11 at 18:16
1  
If an EMPTY STRING has no business use (ie: a product name MUST be provided - where in this case say NULL is acceptable to indicate N/A - so that the workflow is complete), then the logic layer (usually the business layer, or in some case the view/UI layer - always depending on your application architecture of course) should hinder blank strings for the field(s) being entered - with as you say, a warning to the user such as "Field Required", and pausing the workflow until the required data has been entered. If using ASP.NET for example, you could use the handy Validation controls for that. –  Martin S. Stoller Jul 20 '11 at 18:40
2  
+1 on this, having been a developer that is asked to maintain existing software I loathed with a passion when a data layer magically altered what I explicitly asked it to save. Caveat is if the data layer actually incorporates some of the business layer and enforces business rules (though it doesn't sound like this is the case here) –  Andrew Bickerton Jul 21 '11 at 8:02
add comment

This is where theory meets practice: it's a big grey area

Thoughts:

  • NULL goes into the record NULL bitmap which always exists. An empty string takes 2 bytes, so NULL is more efficiently stored
  • IS NULL checks use the NULL bitmap = an optmisation
  • When indexed, a NULL adds a NULL bitmap to each index entry = larger index. Empty string won't (still 2 bytes though).
  • Consistency is king
  • (MyCol IS NULL OR MyCol = @MyCol) is now quite optimised in SQL Server

Personally, I'd use NULLs and avoid empty strings unless there is a good reason for doing so. Mainly for sanity and consistency. Bollocks to theory.

share|improve this answer
    
The developer for the middle tier has offered to implement a method that will convert all empty strings to NULL. I'm wondering though, is it the responsibility of the database to ensure this consistency? Or is it my responsibility to represent what was submitted (correct or not)? –  8kb Jul 20 '11 at 18:21
1  
@8kb: "never trust input"? I would always ensure that NULL is written, even if empty string is sent. –  gbn Jul 20 '11 at 20:05
1  
@gbn The developer in me agrees with "never trust input", but the DBA in me knows that input is sacrosanct (ie: should not be changed and thus corrupted in any way). Think about it like this: You make a backup of your server and expect to get the same 100% on restore - you aren't expecting the backup software to empirically decide to, let us say, correct all spelling mistakes it finds. In a way, a DB is a "backup" of the data you pass to it. –  Martin S. Stoller Jul 21 '11 at 17:45
add comment

If this is a procedure used by business in general then leave it as is communicate to the development team the need to clean up their dirty data.

If this was created to support a specific project, then talk with the project team and determine how they want the data stored. The result should be to have them clean up their data but there may be a reason they are unable to.

share|improve this answer
add comment

Since an empty string has no meaning of its own in your case, and you have already sufficiently communicated with the developers who provide the inconsistent data, then I think you can safely convert that string to NULL in this case.

Of course, as others pointed out, you cannot make this a general rule.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.