August 24, 2010 at 7:03 am
Blah Forums flippd out on me for a moment... this was a double post of my other one. 🙁
August 24, 2010 at 7:25 am
The title of the article says it covers the handling of NULLs in SQL 2008. Is there any difference between SQL 2005 and SQL 2008?
-- Kit
August 24, 2010 at 7:26 am
Carlo Romagnano (8/24/2010)
SQL2008???What's the difference from the other versions of sqlserver or ANSI SQL?
I agree - the headline was a "must-read", because it implied a change. I don't see that in the article.
August 24, 2010 at 7:30 am
Kit G (8/24/2010)
The title of the article says it covers the handling of NULLs in SQL 2008. Is there any difference between SQL 2005 and SQL 2008?
As far as I know there is no difference.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2010 at 7:35 am
da-zero (8/24/2010)
Carlo Romagnano (8/24/2010)
One way to avoid worrying about NULLs is never to use them, ...declaring columns as not allowing NULLs and designating default values for "empty" or "unknown"...
...VERY TERRIBLE!
Please elaborate why this is so terrible, that way we can all learn something.
This is odd advice, at the very best. If you need to deal with empty or unknown, NULL is a sensible way of doing that; much more so that "0 doesn't mean 0" logic. The author seems to include this advice rather off-the-cuff. He doesn't elaborate on the kinds of information architecture that would lead towards preferring default over NULL.
Sometimes a record must contain non-NULL values to be usable; defaulting that may allow the logic around missing values to be contained, rather than spread out. But one has to consider the implications - is the default in the normal range? Will there ever be a need to change the default? For example, an expiry date for a subscription. What if you sell a "lifetime" subscription?
A fair number of the real Y2K problems came from the use of "in-band" defaults as sentinels - 99 in the date field was once common for "stop here"; in 1998 a lot of people had to clean that up. NULL might be a better choice than any default for a "special" date. Every real date (post 1752) will eventually occur, after all.
August 24, 2010 at 7:50 am
"One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values."
This is why I rated this article poorly. This article started out reading as if written as an introduction to a query-writing analyst, and while I thought the introductory paragraph was weak, it at least made sense given who I thought the audience was. But then the article reaches this point where it seems to change to addressing a data modeller. But to save keystrokes, presumably when writing queries? This reeks of cargo-cult programming advice.
There is a distinct advantage to using Null rather than a sentinel value or magic value: While the author asserts ternary logic is difficult, I believe this is just a matter of familiarity. In the world at large, there is a consensus to the truth tables of ternary logic, and they can be found easily as a resource (to wit, this article). It's a convention. A sentinel value is the implementer's business rule. There is not necessarily a convention to them; in fact, the meaning may very well be overloaded based on context. This becomes a maintenace nightmare.
If the organization's documentation is rock solid and you have an ironclad change management process, do what your organization does; if the organization is prone to cowboy coding, please have the courtesy to those who may have to perform maintenance on your system to never come up with a sentinel value on the fly.
I'm tired of digging through views that query other views that rely on some obscurely named field happening to be not negative or less than 99999, etc.
August 24, 2010 at 8:13 am
Nicely done Adolfo. Very clean article with clear examples and descriptions.
August 24, 2010 at 9:57 am
A very useful article except one small misprint:)
select * from tbl
where isnull(boolean,1)= 0
I tested, it should be:
select * from tbl
where isnull(boolean,0)= 0
August 24, 2010 at 10:11 am
This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, The Relational Model For Database Management, ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.
August 24, 2010 at 10:20 am
tim.stevens (8/24/2010)
This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold.
And as wrong as ever. Just using DateTime Fields I can think of a large number of uses (subscription termination date for a life time subscription, project completion date for a project, task completion date for a task on a project,etc ) just off the top of my head.
Far better to use NULL for what it is than to use a magic value. *shudder*
August 24, 2010 at 10:29 am
mtassin (8/24/2010)
... Just using DateTime Fields I can think of a large number of uses ...
Far better to use NULL for what it is than to use a magic value. *shudder*
It strikes me that you can "hide" the nulls in joins or in bit fields, or you can keep them in the values.
(You hide a null in a join by not creating the termination date row, for example.)
The object of the architecture should be to make the project work and be clear for maintenance. Within that, I'm not doctrinaire about very many things. But magic values are generally a very poor strategy.
August 24, 2010 at 10:35 am
My point exactly. I never advocated the use of "magic values" but rather the use of a well-defined architecture. The judicious normalization of a data model, coupled with appropriate joins, will deliver the same information regarding the presence or absence of data without having to muck about with using NULL AS a magic value of sorts (lifetime subscription, incomplete project, etc.)
August 24, 2010 at 11:34 am
tim.stevens (8/24/2010)
This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, The Relational Model For Database Management, ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.
Though it is indeed possible to create a relational database with no NULLs anywhere in the base table, this comes at the expense of a huge increase in the number of tables. And hence in the number of joins required for almost anything.
And the gain is way too limited. As soon as you start combining data from these NULL-less tables, you'll get them right back, because you have to use outer joins to keep your data.
The academics who want a database without NULLs remind me of one of my first projects in an insurance company. We had been appointed the wrong people to talk to. They told us that every person in the database should have birthday and several other attributes known, so we made them mandatory. Within hours of deploying, we had a visit from people from the actual work floor, showing us application forms where the birthdate and/or other mandatory fields were not filled. Sure, they had to be added before the application could be granted and converted into an actual policy - but the application had to be in the database first, so that the people in the acceptance department could phone the agent about the missing information.
(Luckily, the change from NOT NULL to NULL was very easy. The front-end people had a harder job!)
It's all fine to say that NULLs are not needed in theory. Theory is not practice. In practice, a business is confronted with missing information all the time, for various reasons. Any application needs some way to deal with that. And until someone finds a better solution than NULL, I'll defend NULL.
August 24, 2010 at 11:56 am
And a well-issued defense at that! I believe that you are referring to the credo of "Normalize until it hurts, denormalize until it stops". I have one client that has a database that was architected for them that was indeed normalized ad infinitum to where a simple query may entail upwards of 8-10 tables! Clearly, this is an empirical case of "enough is enough".
There is, of course, a decent middle ground, in which a certain amount of judiciousness can eliminate spurious nullable columns in which all but a handful are forever left as NULL for the sake of the few exceptions. As with most other disciplines, in database architecture it is in the capacity of making translation from theory into practice that makes theory valuable. While I will admit that NULLs are here to stay, at the very least Adolfo's bold assertion can give us pause to think about using some discretion in applying them.
August 24, 2010 at 2:06 pm
"The Semantics of NULL in SQL Server 2008 " -- title is very misleading. Behavior of NULL is same in SQL 7, 2000 and 2005 versions as well. Unless I am missing something, What is so different in 2008 about NULLs?
Viewing 15 posts - 31 through 45 (of 68 total)
You must be logged in to reply to this topic. Login to reply