SQL Server NULLs and their impact

This is part rant, part discussion…

In this modern world, developing database driven applications has become pretty damn straightforward, even for us .NET coders. Start a project, grab a ORM framework of choice (Subsonic, Entity Framework, Linq 2 SQL), define a database, create your tables, generate your classes and POW! Job done.

Which is awesome. Apart from the fact that your class behaviour is now tightly defined to your database behaviour (by default, I appreciate you can change this). Which means in turn, you really should be taking great care in defining your databases, since the closer this maps your domain model, the easier your job becomes.

So why on earth leave your columns set to NULL unless you actually intend to allow NULL values? I appreciate that SQL Server allows a column as null by default, but it has so many implications if you’re not intending it it’s untrue. Lets take the following examples:

int? Age { get; set; }

if (Age > 0)
{
    Console.Writeline("This is awesome folks!");
}
int Age { get; set; }

if (Age > 0)
{
    Console.Writeline("This is awesome folks!");
}

Clearly the two are not equivalent. The first wont even compile, since the compiler has enough sense to recognise that int? could be null and therefore the expression int? > int is not valid.

It could be argued that Age may indeed be nullable, nullable indicating a genuine lack of this piece of knowledge. If this is the case, then we need to take that into account.

class="brush: csharp; title: ; notranslate" title="">int? Age { get; set; }</p>

if (Age == null) Console.Writeline("We don't have an age for this person..."); else if (Age.value > 0) Console.Writeline("This is awesome folks!"); </pre> In this case we have a potential for three different things to be happening, all of which need to be taken into account. Each one is a semantically important case which requires understanding and testing. But often a field being left nullable is just a sign of laziness on the part of the database being developed. The giveaway is when the following code is spotted:

int? Age { get; set; }</p>

if ((Age ?? 0) > 0) { Console.Writeline("This is awesome folks!"); } </pre> The ?? operator is an expressive operator used to define a default behaviour, not to correct a mistake made elsewhere by the developer. It would seem common sense to go back and change the model so that the code reflects the first example, but more often than not I see developers opting for option above. Please use NULL wisely.