SQL Server Lookups and Enums
We are told in database design class that normalisation is a good thing. We
shouldn't repeat values, for many reasons including performance and
maintainability. This means most of us have reference/lookup tables to hold
lists of values. These tables will generally have 2 columns, a surrogate primary
key normally the smallest integer data type as possible and a description column
create table PaymentType (PaymentTypeId tinyint not null ,PaymentTypeDescription varchar(255) not null ,constraint PK_PaymentType primary key (PaymentTypeId) )
What this means is that the related tables contain lots of foreign key columns that one there own have no meaning, they just contain a bunch of
create table Order (OrderId int not null ,OrderDate datetime not null ... ,PaymentTypeId tinyint not null )
If you want to retrieve data from the related tables based on the lookup value you have two options. You can either hard code the surrogate keys in your code, or join to the lookup table and use the description.
select Order.OrderId, Order.OrderDate, Order.PaymentTypeId from Order where PaymentTypeId = 3 --Invoice type
select Order.OrderId, Order.OrderDate, Order.PaymentTypeId from Order join PaymentType on Order.PaymentTypeId = PaymentType.PaymentTypeId where PaymentTypeDescription = 'Invoice'
Neither of these is ideal. The first makes your code un-readable (unless you use comments) and will only work if you have inserted the data into the lookup table with the same surrogate keys. If you have used an identity column this becomes much more difficult, especially when you have multiple environments.
The second option adds a level of complexity to all of your queries and if you have multiple lookups can affect performance.
In SQL Server 2000 and SQL Server 2005 where my application needs to perform lookups like the examples above I change my lookup table to use a meaningful surrogate key. I do this by changing the surrogate key to a 4 character column. This size is a provides data storage the same as an integer and should allow for meaningful values.
create table PaymentType (PaymentTypeCode char(4) not null ,PaymentTypeDescription varchar(255) not null
,constraint PK_PaymentType primary key (PaymentTypeCode) ) create table Order (OrderId int not null ,OrderDate datetime not null ...
,PaymentTypeCode char(4) not null )
Your code can then look like this,
select Order.OrderId, Order.OrderDate, Order.PaymentTypeCode from Order where PaymentTypeCode = 'INVC'
This is still not ideal because even if you only have 10 lookup values you are still using 4 bytes to store the surrogate key, when you could have used a tinyint and only used 1 byte
What would be great would be able to have the best of both worlds, be able to
use meaningful in code whilst maintaining the performance of small integer
If you have programmed outside of TSQL i.e. C# or VB then you should now be
muttering something along the lines of "what we need is enums". Spot on, that is
what I am suggesting "Allow literals and read only table data to be represented as enums"
select Order.OrderId, Order.OrderDate, Order.PaymentTypeCode from Order where PaymentTypeId = PaymentType.Invoice
If you think it would be good to be able to do the following then please vote
Simon is a Independent Consultant focusing on database architecture.
He specializes in performance SQL Server systems and recently on search technologies.
To keep up on Simons thoughts on SQL
Server and other database related topics, read his blog
SQLServerCentral article by Steve Jones