123 Eng

Engineering the engineers™


Latest Jobs   Forum Map

 


Home

Source Codes

Engineering Colleges

BE Students

Training  Reports (updated)

Seminar Reports (updated

Placement Papers (updated)

Forums

   Computer Science / IT

   Electronics

   Electrical

   Mechanical

   Chemical

   Civil

   CAT / MBA

   GMAT / Foreign MBA

Latest Jobs

Engineering Jobs / Technical Jobs

Management Jobs

Sitemap

About-Us

Terms of use

Displaying  Source Code(s)  
 

 
Why should I avoid NULLs in my database?

--------------------------------------------------------------------------------



Joe Celko said it best: "NULLs confuse people..." (SQL For Smarties, ISBN 1558605762). McGoveran and Date add:<BR>NULLs...are far more trouble than they are worth and should be avoided; they display very strange and inconsistent
behavior and can be a rich source of error and confusion." (Guide to Sybase and SQL Server, ISBN 020155710X).
My sentiments exactly. Of course, I don't expect to convince you by flashing a few quotes from very reputable
authors in front of you. Let's talk for a minute about what exactly NULLs do that cause this type of reaction. The first
problem is that the definition of NULL is "unknown." So, one problem is determining whether one value is (not)
equal to another value, when one or both values are NULL. This trickles down to many problems for a database
engine and any associated applications. The following list details some of those problems:
@li they are interpreted differently depending on compatibility level and ANSI settings;
For example, let's consider two values, x and y, that are both NULL. Since the definition of NULL is
unknown, then you can't say x = y. However, with the ANSI setting ANSI_NULLs, this can be
different. When this setting is FALSE, x = y ... however, when TRUE, x <> y. Confusing, no?
@li the storage engine has to do extra processing for each row to determine if the NULLable column is in fact
NULL -- this extra bit field affects storage and indexing, and obviously has performance implications for
general queries;
@li they produce weird results when using calculations, comparisons, sorting and grouping;
@li they create problems with aggregates and joins, such as different answers for COUNT(*) vs.
COUNT(fieldname);
@li they produce unpredictable results in statistics computations, particularly WITH ROLLUP and WITH CUBE;
@li applications must add extra logic to handle inserting and retrieving results, which may or may not include
NULL values;
@li they cause unpredictable results with NOT EXISTS and NOT IN subqueries (working backwards, SQL
determines that NULL columns belong or do not belong to the result set, usually for the wrong reasons);
@li no language that supports embedded SQL has native support for NULL SQL values.

--------------------------------------------------------------------------------

 

 

Contribute content or training reports / feedback / Comments
job placement papers
All rights reserved copyright 123ENG