February 25, 2008 at 1:49 pm
Matt:
I think the discourse runs like this:
* Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft
* A relational database is a database that conforms to the relational model
* The relational model for database management is a database model based on predicate logic and set theory
* The predicate logic used is First-Order
* A First-Order predicate is either a relation or the boolean-valued function that amounts to the characteristic function of a relation.
Hence n-adic relational databases are possible - but SQL is not one of them. Well not supposed to be anyway. Of course NULL subverts the relational model since the predicate function P: X? {true, false} is now P: X? {true, false, NULL}.
Now either SQL IS relational (and so CANNOT by definition use triadic predicates). Or it is Tri-adic - in which case it needs all the 3^3 tri-adic operators. Which it don't have.
The result is a sort of hybrid where - under the bonnet often - SQL applies some of the tri-adic Truth table to its transformations and functions. Sometimes.
Matt wrote:
"What would you advocate as "the correct way" for handling the age issue from above?"
Well my answer would be fitness for purpose i.e. what do you want to use the data for:
* IFF the only possible answers to question allow True/False predicate logic then no problem
* IFF not then use a data type whose domain and operators cater for the possible answers
In a better world this would be part of SQL Server - indeed I believe that "user-defined domains" are part of the "relational model. Not a "nice to have" part but an intrinsic part of the relational model.
For whatever reason SQL (and i believe Oracle) do NOT implement "user-defined domains" so the designers were forced into the bodge of inventing NULL. Since NULL can appear within a column of any data type it is typeless - hence operators should (and do ) fail to work with it (since operators are associated with types) hence IS NULL etc etc/.
SO - given the bodge - how do people deal with it in the real-world??
Hence my simple poll. BTW is it possible to complete my multi-choice poll and where can I see the results? !
It is now 20:45 in the UK and time to watch "House".... Thanks for the good-natured and intelligent discussions (you should see the immature flame-wars on some SQL sites :crazy:) and long may they continue.
February 25, 2008 at 2:45 pm
I'm not sure how you're getting here - but the results of the poll are being displayed graphically above the posts (in IE at least.) Currently 18 different folks have voted.
And - ultimately - no arguments necessarily to anything you said. No doubt in my mind that SQL is a hybrid solution of sorts. That being said - I'm not sure that anyone has managed to adequately map all of the theoretical requirements into anything robust enough to handle enterprise-sized data and not fall on its face performance-wise. (I mean - 6NF is theoretically the mother of all answers but it doesn't scale worth a d**n in today's implementations). CJ Date and his beloved third manifesto (third go round at the third manifesto at my last count, which would make it the FIFTH manifesto in my book) always seems to end with "and none of this works on any scale of any significance in anything in production today"... So - nice theory, but that and fifty cents probably won't buy my coffee....:P
Until then - I deal in what I can work with, compromises and all...:) That usually entails defining the parameters making NULL "okay" in any number of scenarios, and what to do with it when it's encountered.
----------------------------------------------------------------------------------
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 25, 2008 at 3:35 pm
Andrew (2/25/2008)
Hence my simple poll. BTW is it possible to complete my multi-choice poll and where can I see the results? !
It is now 20:45 in the UK and time to watch "House".... Thanks for the good-natured and intelligent discussions (you should see the immature flame-wars on some SQL sites :crazy:) and long may they continue.
By the way - you mean THIS kind of flame war?
http://qa.sqlservercentral.com/Forums/Topic339807-236-1.aspx
yup - it's occurred here too. Several times over....
----------------------------------------------------------------------------------
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 25, 2008 at 11:40 pm
I'm with Jeff on this one. There are advantages to using NULL in certain circumstances - appropriately used NULLs are a great tool, particularly for aggregates (e.g. Average, sum, etc.) Though I'll admit to having spent a lot of time over the years explaining what NULL is/isn't.
February 26, 2008 at 12:48 am
Thanks Joe.
Joe wrote "....lot of time over the years explaining what NULL is/isn't."
Actually this is my main concern, UP to a point its alright SQL database DBAs and experienced developers guarding against NULLs - if I may put it that way. (But hands up who has not been caught out by NULL - in the last week ? month ?)
The problem is inexperienced database users - such as dotNet developers. Here I must express an interest as I have been a freelance OO developer for 12 years. In my experience dotNet developers write AWFUL SQL statements with either a disregard for NULL - or COALSESCE peppered at random in SELECT statements.
It does indeed take a lot of time to explain NULL and it ALWAYS catches you out sooner or later...
In programming I have learnt to avoid the "wicked wheeze" that gets round some difficult programming problem - it always returns to haunt you sooner or later. My current work involves a wicked wheeze where by a database field value might be "01/00" and the application parses this into 2 separate object properties with values "01" and "00". (Atomic values - huh !)
Turns out there is no validation of input so there are values like "01:00" and "01 00" in the data and you have to guess if the parser parses these correctly....
NULL is just such a wicked wheeze - it attempts to partially fix an incomplete implementation of a mathematical model and in doing so creates problems of its own making. Bear in mind that SQL was designed by a committee...
February 26, 2008 at 8:01 am
I personally love NULLs.
I never developed an application where all the data was always available or known and NULLs are perfect for such situations. I don't fully agree with Andrew in that the .NET people have a problem with understanding or using NULLs. I think that OO programmers have no problem dealing with NULLs because they have to deal with the concept of Nothing. I was a part of a .NET development team where most of the team members had no SQL experience but they were all superb OO programmers.
They all were writing good SQL code in just few months of tutoring and no one had a problem with using NULLs.
And my next statement might be considered as rude but I think the people who might have problem with NULLs are half cooked programmers who just learned how to use some programming tools but do not understand the basic concepts of programming and how the computers work. Unfortunatelly due to the ease of using Microsoft technologies we all see a ton of people who are very proficient with using the MS tools but have no concept of what is really going on behind the pretty screens...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 26, 2008 at 8:28 am
Jack Wrote " ... just few months of tutoring "
Well exactly - a few months of tutoring!
Jack Wrote " ... never developed an application where all the data was always available or known "
Well of course - that is the real-world situation for all of us in all that we do.
Jack wrote "... think the people who might have problem with NULLs are half cooked programmers "
I agree in principle but personally would have phrased this as "people who are under-trained and under-managed". Of course this happens in the real world...
Jack Wrote " ... and NULLs are perfect for such situations"
Perfect? Or pragmatic? Not space here to illustrate the logical, syntactic and pragmatic difficulties of using NULL.
A perfect solution to incomplete information would not, I suggest, involve "months of tutoring". Nor would it require taking precautions to avoid the negative consequences of its "perfection".
JAck wrote " OO programmers have no problem dealing with NULLs because they have to deal with the concept of Nothing"
Actually the two issues - NULL and NOTHING - are quite different in cause, concern and usage, although I agree they have a similar "flavor". One major difference is that NOTHING is not (generally) created by having to model incomplete information in the real world but by the fact that objects are created, terminated and initialised.
BTW a question I have asked several times with no replies as yet - how do you ensure that the output of a query is now and for ever correct ? (This is not quite the same as testing).
February 26, 2008 at 8:39 am
BTW a question I have asked several times with no replies as yet - how do you ensure that the output of a query is now and for ever correct ? (This is not quite the same as testing).
Now and forever is something you will never get assurances on in any circumstance. Things evolve, period. Every programming language changes over time. Thinking something will never change, or even that promises that said syntax will NEVER change, or that the optimizer will never change how it deals with that, well - that's about on the same level as the Easter bunny.
----------------------------------------------------------------------------------
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 26, 2008 at 9:02 am
Matt:
OK OK 🙂
Given the same database schema with the same evnironment then...
The point I was trying to get at was that a correct mathematical proof is inviolate to time. Since relational databases use a closed relataional algebra it ought to be possible to formally prove that a relational database query is correct - and hence will be correct for "all time".
Well - SQL is NOT a (fully) relational database and:
* the optimizer does NOT use relational algebra per se
* the consequences of NULLs are quite hard to predict mathematically (mulit-valued logic)
* the interplay between non-relational SQL and NULL is even harder to sort out
Now I realise that a) most database developers are clever and well-trained and b) all developers optimstically over-state thier skills ("yes - should only take an hour, have it with you by lunch time...") and c) we all have extensive unit and system tests we can run.
Still - I do wonder how many queries are running producing reports with incorrect information and HOW WOULD ANYONE KNOW ?
FINALLY - I would like to talk to any database developer who hand on heart has NEVER EVER had unexpected results from a query that turned out to be due to the presence of NULL.
February 26, 2008 at 10:19 am
Andrew.
Just few things to clarify. When I stated that they required few months of tutoring it meant that from the point of zero knowledge of SQL to the point where they were very efficient it took just few sessions of SQL tutoring over the period of few months. But this not the point. The point is - they never had problem with the concept of NULL.
As far as the half cooked comment - yes, I like your politically corrected version but I do not buy the explanation that 'I don't know something because my company did not train me on it'. I am a professional and I expect myself to gain the knowledge I need to perform my job and to grow regardless of the environment I work in.
On your last question in the last post. Show me a SQL programmer who did not have a query producing the the wrong results - period. Regardless of the NULLs. NULLs are not the root cause of the problem. If you mentally accept them and think of them as 'a part of SQL life' you will see that they provide you with a lot of power, not available without them. When I design a database I try to use the NULLs to my advantage, I plan on using them, I count on the NULLs to be in the database. I think I would feel crippled if I did not have them and my disposal.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply