Enums, SQL Server, and Null

Enumerations are great, because they give you a human-readable, strongly typed, limited list of values to use (instead of, say, an integer with a symbolic value). But how do enumerations work with SQL Server, and in particular, with null?

The History of Enums: C++ and Integers

Historically (especially in C++), enumerations came about to give a strongly-typed, limited-size list of values to use instead of integers. Consider code that returns 0 for success, 1 for “File not found,” and 2 for “out of space.” Wouldn’t you much rather prefer values like, say, Success, FileNotFound, and OutOfSpace?

Worse still, what if you’re using 1 to represent some value, and you accidentally assign some other value, or call i++? There’s no safety net.

If you just learned C# (without the pain of running into this in C++), you might not realize that enums can be easily converted into integers. Not to mention that each value already has a (default, overridable) integer value assigned to it.

But that’s besides the point. What’s more relevant is that enums are usually stored as integers in SQL Server. I say usually because there are probably a dozen different ways to handle the implementation — but the simplest is just to use integers. This is especially true if you’re using some framework that maps the values to integers.

Dealing with Null and SQL Server

One drawback of enumerations is that you can’t easily specify “no value” or “nothing.” Sure, you could add a special Nothing value to your enum, but what would be the point of that?

As it turns out, there is a way to specify a nothing value. Any enumeration can easily be defined as a nullable type, by use of the ? symbol.

If you’re not familiar with the question-mark nullable operator, it means “and also possibly null. To illustrate:


int x = 13;
x = null; // compilation error
int? y = 7;
y = null; // works!

You can do the same thing with enumerations:


private enum DayOfWeek { Sunday, Monday, ... , Saturday };

DayOfWeek today = Sunday;
today = null; // won't compile
DayOfWeek? tomorrow = Saturday;
tomorrow = null; // compiles!

The MSDN documentation mentions that any value type can be nullable — ints, bools, floats, etc. and enumerations.

And as you would expect, this allows you to specify a null value in your column in SQL Server. No extra work needed!

Summary

In this brief post, we talked about enumerations: their purpose historically, their relationship to integers, SQL server, and how to make any enumeration null-worthy by using nullable types.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Core .NET, Silverlight, Web, Wndows Forms, WPF and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *