Access Tip of the Day


Avoid using NULL values in your databases


In our opinion (and in the opinions of many database experts), NULL columns are used far too often in most databases. In fact, you're better off avoiding them altogether. Why? Here's a short list of some of the problems you can expect to encounter if you use NULLs in your databases:

* Client applications require extra programming logic to handle them; NULL values often cause errors in client programs.
* Calculation, sorting, comparison, and grouping operations can handle NULL values in unexpected and counterintuitive ways.
* Aggregates and join operations also exhibit unexpected or counterintuitive results (e.g., COUNT(*) and COUNT(MyField) produce different results).
* NULL values can lead to undesirable results for WITH CUBE, WITH ROLLUP, and other statistical operations.
* Nullable columns that contain NULL values cause a small but measurable sacrifice in performance since the database has to perform an additional check to determine whether the column allows NULL values.

So, in general, you're much better off avoiding NULL. Instead, use values such as 0 for numeric columns or an empty string ('') for character data. This will make the job of developing enterprise database applications a whole lot easier.



(Content provided by Element K Journals)


(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
 
PrintPrint CloseClose

Copyright © 2001 - 2022 MJ Technologies, LLC.  All rights reserved.