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.

  • 3
    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
    – user1822
    Jul 20, 2011 at 17:11

4 Answers 4


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

  • @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, 2011 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. Jul 20, 2011 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) Jul 21, 2011 at 8:02

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


  • 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.

  • 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, 2011 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, 2011 at 20:05
  • 3
    @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. Jul 21, 2011 at 17:45

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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