Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Null Defaults


Null Defaults

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Administrators
Points: 52356 Visits: 19006
Comments posted to this topic are about the item Null Defaults

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
n.ryan
n.ryan
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 177
While I'm quite an advocate of using NULL values instead of magic values, due to the slightly interesting way that NULL values are handled in comparisons and sorts there are times when magic values are more appropriate. These cases usually revolve around the situations where non-technical users use and require fairly direct access to the data, usually for reporting purposes and usually date based columns. While it's easy enough to create views into the data and through these effectively implement magic values for these columns this process adds another administrative burden to a system.

For the majority of non-date/time based there really shouldn't be a need for magic values. Sometimes pseudo-magic numbers are useful though, for example where a status lookup ID has an implied order where for example 0=don't know (default, non null) and 999=hell yeah, it makes queries rather simpler if they can be ordered by the ID direct rather than a sort column in a joined lookup table. Again, this situation could be handled in a view to make life easier for end users but as above this adds an administrative burden (and also makes query optimisation a little more involved as well).

So I'd say that it's best to avoid both magic values and NULL values where possible. Magic values should really be handled using lookup references and default values (this tends to be hard with date/time columns) and NULLs avoided unless you genuinely need to record that no value has been provided, just beware of the consequences.
dsor
dsor
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 402
I would always use null to indicate unknown values, but in this case I don't quite see when you would legitimately not know if null is a valid value or not when putting your data model in production. During early development I can get behind going null by default until you start working on the DAL, then you need to start defining the contract imo.
adam.sewell 53546
adam.sewell 53546
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 4
Glad to see some good, sensible practice being advocated in the first two comments. I feared seeing a load of magic value recipes.
My comment is that this situation is exactly suited to NULL values. NULL means that something is missing, unavailable, not supplied and is a case that must be dealt with properly. We all hate writing code with loads of ISNULLs or IS (NOT) NULL etc. and even more tiresome is dealing with it inside the application, but missing does not equal '', '1900-10-01', zero 0 or -1 it's NULL!
If a value added to a table simply can't be NULL it must be defaulted to a meaningful value or that situation handled in the DAL or business logic.
The fear of NULL is the result of lazy or worse ignorant programmers. It's nothing to worry about people, just write proper code!
I totally agree with the points above regarding sorting and end user access, no rules are inviolable, but magic values are poisonous to me. Where users have access to data, I try to abstract away the complexities, including NULL values and outer joins where possible.
Let's show some love for NULL in all its painful reality :-)
Christy M
Christy M
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1151 Visits: 211
I would advocate going down the NULL route. It's a new column after all and when that column is referenced by the calling application, that application should know what it wants to do with it or should handle it properly by setting it aside if it doesn't need it.
Ed Dee
Ed Dee
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 81
Using a 'magic value' to represent an unknown value generally does not seem to be a good idea. Most of us have been in the situation where the user has states categorically that a certain case will never exist only for it to materialise a couple of years down the track as a legitimate case. If for some reason some particular end user interface cannot handle null values(purists will say null is a state rather than a value), then provide a view which CASTs the null to some value acceptable to that user.
Early drafts of the ISO (ANSI for Americans) SQL standard incorporated the concept of 'multi-valued' null to allow the differentiation of missing values, known unknowns not relevant etc... but the concept was dropped as none of the implementors were interested in providing it. That proposed facility originated for the earlier ANSI work on multiple types of null
Terje Hermanseter
Terje Hermanseter
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1050 Visits: 568
I like NULL as the deault choice. All other values have a tendency to create confusion at some point in the future in my experience. Most people have a good understanding of what NULL values mean.
Mark Dalley
Mark Dalley
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 1609
Terje Hermanseter - Friday, March 3, 2017 2:51 AM
I like NULL as the deault choice. All other values have a tendency to create confusion at some point in the future in my experience. Most people have a good understanding of what NULL values mean.


I think NULL is the logical choice too, but I would also say that "knowing what NULL means" is pretty context-dependent! Take an employment termination date in a personnel record. While the employee is still employed, the termination date should (I suppose) be null, but that doesn't mean it's unknown, it means that because it hasn't yet happened yet it doesn't exist at all! Perhaps this is too much of a straw-splitting distinction.

Sorting NULL fields can be a pain though. Often you want them to sort before any other date, as SQL Server does by default, but other times (as in the case of the termination date above) you want it to sort after. Hence the unwelcome appearance of magic values (one system I use uses a date in the year 2500 for this sort of thing).

What might be cool would be to take a leaf from PostgreSQL's book and introduce two new values: positive infinity (i.e not NULL but it sorts after everything else) and negative infinity (not NULL but sorts before everything else). I've never actually used PostgreSQL so can't speak from experience, but it seems like a good idea. Some purists would no doubt disagree and I respect that. I have no interest in starting a religious war.

I think that the root of the problem is that when you have several concepts (unknown, doesn't exst, not defined, ...) which are easily confused, context dependent and generally slippery to work with, trying to use just one thing (i.e. NULL) for all of them is necessarily going to involve a few compromises. And some extra work.

MarkD
Stefan LG
Stefan LG
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 112
I have always used NULL values, although I have seen some (heated) arguments over the years against it!
Unfortunately nobody has been able to convince me yet Wink
There are more than enough functions available in SQL to handle nulls - NULLIF, ISNULL, COALESCE, IS NULL, IS NOT NULL etc.

I agree with some of the other comments that null values should indicate 'missing' and/or incomplete data.
For example: Let's assume you want to log the start and end time of an event. The start time might be available when the record is inserted, but not the end time.
So a quick query for null values in the end time column will show you events that are still waiting to be completed.

PS. Reporting Services also support null values which can be used to your advantage.
Gary Varga
Gary Varga
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13703 Visits: 6467
[quote]
Mark Dalley - Friday, March 3, 2017 3:35 AM
...Take an employment termination date in a personnel record. While the employee is still employed, the termination date should (I suppose) be null, but that doesn't mean it's unknown, it means that because it hasn't yet happened yet it doesn't exist at all!...

Which can be viewed as at the current point in time it is unknown. N'est pas?


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search