title: Standard use of 'Z' instead of NULL to represent missing data?
body: <p>Outside of the argument of whether or not NULLs should ever be used: I am responsible for an existing database that uses NULL to mean &quot;missing or never entered&quot; data.  It is different from empty string, which means &quot;a user set this value, and they selected 'empty'.&quot;</p>
  <p>Another contractor on the project is firmly on the &quot;NULLs do not exist for me; I never use NULL and nobody else should, either&quot; side of the argument.  However, what confuses me is that since the contractor's team DOES acknowledge the difference between &quot;missing/never entered&quot; and &quot;intentionally empty or indicated by the user as unknown,&quot; they use a single character 'Z' throughout their code and stored procedures to represent &quot;missing/never entered&quot; with the same meaning as NULL throughout the rest of the database.</p>
  <p>Although our shared customer has asked for this to be changed, and I have supported this request, the team cites this as &quot;standard practice&quot; among DBAs far more advanced than I; they are reluctant to change to use NULLs based on my ignorant request alone.  So, can anyone help me overcome my ignorance?  Is there any standard, or small group of individuals, or even a single loud voice among SQL experts which advocates the use of 'Z' in place of NULL?</p>
  <h2>Update</h2>
  <p>I have a response from the contractor to add.  Here's what he said when the customer asked for the special values to be removed to allow NULL in columns with no data:</p>
  <blockquote>
  <p><em>Basically, I designed the database to avoid NULLs whenever possible.  Here is the rationale:</em></p>
  <p>• <em>A NULL in a string [VARCHAR] field is never necessary because an empty (zero-length) string furnishes exactly the same information.</em></p>
  <p>•  <em>A NULL in an integer field (e.g., an ID value) can be handled by using a value that would never occur in the data (e.g, -1 for an integer IDENTITY field).</em></p>
  <p>•  <em>A NULL in a date field can easily cause complications in date calculations.  For example, in logic that computes date differences, such as the difference in days between a [RecoveryDate] and an [OnsetDate], the logic will blow up if one or both dates are NULL -- unless an explicit allowance is made for both dates being NULL.  That's extra work and extra handling.  If &quot;default&quot; or &quot;placeholder&quot; dates are used for [RecoveryDate] and [OnsetDate] (e.g., &quot;1/1/1900&quot;) , mathematical calculations might show &quot;unusual&quot; values -- but date logic will not blow up.</em></p>
  <p><em>NULL handling has traditionally been an area where developers make mistakes in stored procedures.</em></p>
  <p><em>In my 15 years as a DBA, I've found it best to avoid NULLs wherever possible.</em></p>
  </blockquote>
  <p>This seems to validate the mostly negative reaction to this question.  Instead of applying an accepted 6NF approach to designing out NULLs, special values are used to &quot;avoid NULLs wherever possible.&quot;  I posted this question with an open mind, and I am glad I learned more about the &quot;NULLs are useful / NULLs are evil&quot; debate, but I am now quite comfortable labeling the 'special values' approach to be complete nonsense.</p>
  <blockquote>
  <p>an empty (zero-length) string furnishes exactly the same information.</p>
  </blockquote>
  <p>No, it doesn't; in the existing database we are modifying, NULL means &quot;never entered&quot; and empty string means &quot;entered as empty&quot;.</p>
  <blockquote>
  <p>NULL handling has traditionally been an area where developers make mistakes in stored procedures.</p>
  </blockquote>
  <p>Yes, but those mistakes have been made thousands of times by thousands of developers, and the lessons and caveats for avoiding those mistakes are known and documented.  As has been mentioned here: whether you accept or reject NULLs, representation of missing values is a <em>solved problem</em>.  There is no need to invent a new solution just because developers continue make easy-to-overcome (and easy-to-identify) mistakes.</p>
  <hr />
  <p><em>As a footnote: I have been a DBE and developer for more than 20 years (which is certainly enough time for me to know the difference beetween a database engineer and a database administrator).  Throughout my career I have always been in the &quot;NULLs are useful&quot; camp, though I was aware that several very smart people disagreed.  I was extremely skeptical about the &quot;special values&quot; approach, but not well-versed enough in the academics of &quot;How To Avoid NULL the Right Way&quot; to make a firm stand.  I always love learning new things—and I still have lots to learn after 20 years.  Thanks to all who contributed to make this a useful discussion.</em></p>