February 19, 2009 at 12:20 pm
Does anyone have any links they can refer which are either associated with some standards group or comitte or even a software publisher (like Microsoft) on use of NULL in Relational DB design and or in T-SQL use?
We have just dealt with yet one more case where the excessive use of ALLOW NULL in the database used by the primary application we use in our bussiness, has resulted in expenditures of resources internally as well as billed support time to track down and correct. I'm trying to make the case to management for the software company that make the application we use that the excessive use of NULLs needs to be curtailed and I would like to have some items I can refernece or point to to back me up on the stance that ALLOW NULL and NULLs in general shoudl be used in limited quantity and with great care and not in excess like as if were tax dollars being thrown around at everything and everywhere for ever purpose.
I know of some good articles on the pitfalls and gotchas of use of NULL by individuals but I was hoping for something more formal by a recognized group or committe like ANSI but not ANSI (unless they have some good published warning about use of NULL).
Thanks
Thanks
Kindest Regards,
Just say No to Facebook!February 19, 2009 at 12:45 pm
The question is are there articles about the evils of NULL values yes but most are by ANSI SQL purists who don't use T-SQL, PL/SQL or Informix 4GL so their intensions are not good. And by mathematicians who also thinks ANSI SQL is crappy so their intensions are worse than the former.
So the question is does NULL values affect performance a little because Yahoo database was all NULL because people on the internet don't give information out until they know you can be trusted.
There are only a few things to know about NULL the aggregates are created for scalar value but a SELECT returns a table so all aggregates ignore NULL except COUNT(*) so you need to use it with all nullable columns.
And OUTER JOIN default to a mathematical NULL because both tables are not equal not related to nullable column.
Kind regards,
Gift Peddie
February 19, 2009 at 1:49 pm
As Gift is referring to - you're treading into one of the long-standing intractable holy wars out there in the RDBMS world (and no - this is not restricted to SQL Server).
I don't know what specific situation you ran into , or what caused you to have to expend the various resources you refer to, but its application can be thorny.
Personally - if you're looking for some solid info on NULLS - the articles put out a few years ago on here by Michael Coles (Mike C.) are top-notch, going into the good, the bad and the ugly involved.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2009 at 2:01 pm
I really like this article on nulls:
http://www.sqlservercentral.com/articles/Advanced+Querying/2829/
April 11, 2009 at 11:24 am
There are many articles on use of SQL Null. Its is NOT jsut a question of "evil ptifalls" - although they certainly exist and can cause problems.
The issue is how to represent Incomplete Information in SQL relational database. OR put another way what does it mean if a column X has a Null in it?
In another forum I suggested this article as a very readable non-academic view on Null - 2006 STEPHEN HENLEY "The problem of missing data in geoscience databases" Computers & Geosciences 32 (2006) 1368–1377.
There are many other articles on this topic. Try CODD 1979 "Missing Informatlon (Applicable and Inapplicable)
in Relational Databases" which pretty much describes the subsequent SQL approach to Null in ANSI 1986 and ISO 1987(contact me directly if you want to know more).
BTW Null is even more interesting in Object Databases since objects (or their properties) which are Null cause exceptions to be raised in OO languages. Most SQL statements of course do NOT cause errors if field values are Null, although the results of the statement may be unexpected (i.e. cause an exception in the user or developer)
regards
Andrew
April 11, 2009 at 11:35 am
There are many other articles on this topic. Try CODD 1979 "Missing Informatlon (Applicable and Inapplicable)
in Relational Databases" which pretty much describes the subsequent SQL approach to Null in ANSI 1986 and ISO 1987(contact me directly if you want to know more).
This was just theory software is about implementation and RDBMS is one of the greate software implementation success stories. Please read Jim Gray's last interview about the technical reasons the vendors implemetation experts changed direction.
BTW Null is even more interesting in Object Databases since objects (or their properties) which are Null cause exceptions to be raised in OO languages. Most SQL statements of course do NOT cause errors if field values are Null, although the results of the statement may be unexpected (i.e. cause an exception in the user or developer)
There are no fields in RDBMS just columns and rows when you get to know columns you will know it is more than a field and for object databases Oracle 8i was object relational ANSI SQL member Jim Melton who is also Oracle VP parted company with the object databases groups because that product was an implementation failure. These are Jim Melton's words not mine.
Kind regards,
Gift Peddie
April 11, 2009 at 12:35 pm
I would take the discussion out of the religious war on the evils of using NULL and place it firmly in the area of data integrity.
It is not so much a question of whether or not nulls are allowed - but, whether or not the application allows the users to not enter required data. If the data is required, then it should enforced at the database level (not null).
If the business requirements state that the data is sometimes required - then you have to allow nulls or some special value to indicate unknown (e.g. 0 for numbers, '' for strings, etc...).
I don't think the problem you had really has anything to do with allowing nulls. It probably has everything to do with the business rules not requiring certain data elements. I would bet that the problem would have occurred regardless of whether or not nulls were allowed. To validate that, ask yourself what would have happened if a special value was used instead. Would it have been any easier/harder to identify the problem and fix it?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2009 at 12:41 pm
You seem to have a downer on "theory".
This "theory" was what was accepted as the way SQL Null was to work.
Field is a term used to describe the contents of a row.
By object database I meant database such as dbo4
April 11, 2009 at 12:43 pm
Gift Peddie - You seem to have a downer on "theory".
Codd's "theory" was what was accepted as the way ANSI SQL Null was to work.
Field is a term used to describe the contents of a row.
By object database I meant databases such as dbo4
April 11, 2009 at 1:01 pm
Thanks Jeffrey. Amazing how similar science is to religion - heresy. orthodoxy, beliefs, holy wars etc.
I am primarily interested in defects in software, which leads to an interest in testing.
There is surprsingly little information about testing SQL statements for correctness and robustness. So I am interested in empirical experiences, especially with regard to Null which used incompetently is an obvious source of lack of correctness / robustess.
Having said that....
I am NOT interested in low-level debates about SQL Null. The flaws in SQL Null are well known. well understood, and well discussed over the last 30 years. A tool should do what it says on the tin. Then it is a good tool in the hands of a competent user.
The commerical imperatives behind the lack of development for handling Incomplete Information in SQL (unchanged since 1992) are also well understood
I am interested in high-level debates about how to represent Incomplete Information in relational databases when the problem domain is more complex than "value not known at the time of entry" or "value not applicable". For example most (arguably all) scientific measurements are imprecise, some are unknowable
I appreciate most business software is essentially transaction processing and models business processes which are (or should be) designed to be simple input-output steps where if data values are not known the transformation will not take place. Banking is an obvious and frequently quoted exmaple.
There are other problem domains however which are not simple transaction processing. Crime investigation software is a good example where the certainty and completeness of information is as important as the actual values stored.
thanks
Andrew
April 11, 2009 at 1:07 pm
Gift Peddie - You seem to have a downer on "theory".
Theory and implementation are usually not related.
Codd's "theory" was what was accepted as the way ANSI SQL Null was to work.
There is just one pesky problem Codd never implemented commercial RDBMS,I think you need to read Jim Gray's last interview, I accept Jim Gray and Jim Melton version of all math related issues because both have implemented RDBMS.
By object database I meant databases such as dbo4
The question is how many businesses run these things? Create a commercial success with more than one million deployed and post again.
Kind regards,
Gift Peddie
April 13, 2009 at 4:05 pm
First off let me thank everyone who took the time to repy back on this; I know the whole NULL issue can ofetn be as dangerous to discuss as politics.
I've brief-cased all the NULL articles at SQL Server Central from Mike C (per one posters recomendation) and while they are very informational what I'm really looking for is something more Official from some kind of entity like something from ANSI or more ideal, something from Microsoft along the lines of 'Best Practices for Null in T-SQL'. This is because in order to make my case for change with the vendor I need something with a more official backing then an independent authors article on the net. Its not that the authors material isn't top nothc or even better then what you'd get from Microsoft. Its just that when you are making a case for change like this there's nothing better to have for backing our stance then official vendor provided documentation.
If anyone else knows of something along those lines I'd love to hear from you.
Thanks again to everyone for pitching in.
Kindest Regards,
Just say No to Facebook!Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply