How To Create A Lookup Table In Sql

Populating Lookup Tables in SQL Server DataTools (SSDT)

Lookup data never really felt like data to me

Definitions

Lookup Table: A table with 2 fields at a minimum, a meaningless primary key and a text value used in normalization to avoid repeating textual values and referenced by foreign keys in other tables. I've also heard them called Reference Tables, and Domain Tables. These tables are not changed during the normal course of business (unlike something like ProductCategory which looks the same but I'd argue isn't a lookup table). This is my definition so don't take this as gospel.

Examples

State table could go either way. That could be maintained by your application.

SQL Server Data Too l s (SSDT): Functionality for developing, testing, deploying, and refactoring SQL Server databases in Visual Studio that doesn't require change script creation. Development is done simply by defining the desired final state of the database and deployment is done by comparing the schema in the project with the schema in the deployment target.

Is Lookup Table Data Really Data?

Think of the corresponding code. It's usually an enum, right? Sometimes you'll implement this as class and load lookup data from the database to avoid hard coding this twice but you're looking for enum-like functionality. Here are a few more tests…

Would your app function if these tables were empty? I doubt it unless you aren't using foreign keys (which warrants a whole other post).
Would a "blank" copy of the database be complete without this data?
Do they change without a corresponding code push?

Ok, I'm feeling pretty comfortable that we're dealing with a really special type of data that sure feels a lot like schema. Now what? We have to get it source controlled and deployed.

Source controlling lookup table data in SSDT

The beauty of SSDT is that we create a declarative model of what a target database should become after a deployment regardless of its current state. We want lookup table data to be treated the same way both in source control and during deployment.

Example

The lookup table

LookupTable.UserStatus.sql

A couple things you may notice…

Lines 5–10 represent the data that is the desired final state of the lookup table

Lines 13–15 are ensuring that we only do an update when something really changed. Lines 13–14 demonstrate the predicate for NOT NULL fields and line 15 demonstrates the predicate for a NULL field. If you've used entity framework you may be familiar with this logic. It allows NULL == NULL == true tests. While this section isn't technically necessary, it's nice to have so you can see (0 row(s) affected) when you run this script against a target table that matches it.

This script is verbose. To create it by hand would be error-prone. I use a script that auto-generates it from metadata and data already in the table. Initially, on large existing DBs I'm migrating to SSDT, I use a PowerShell script that leverages extended properties to identify the lookup tables, creates all of these scripts in the correct order by doing a depth first search of the FK dependency graph, and assumes you're always creating data rather than deleting it (since deletes have to happen in the opposite order. Think Category and SubCategory.) I may publish these scripts at a future time but they're not really ready for dissemination yet. What I'd really love to see is lookup tables, including imports of existing ones, as a first-class feature of SSDT!

Deployment

We want to deploy these using a post deployment script. Since you can only have one, I use the following setup:

Create Script.PostDeployment.sql as Post-Deployment Script. All lookup table scripts, add as Script (Not in Build) to avoid compile time debugging and ensure they all end in GO.

If you accidentally create the wrong type of script and you're getting errors, you can change Build Action to None in properties:

You can also create stored procedures to populate the lookup tables to keep your publish.sql scripts nice and tidy.

Caveats

This will fail in cases where you end up deleting lookup table rows that are referenced by a foreign key in another table. This may happen during a rollback scenario and will require you to disable foreign keys or manually intervene prior to running the scripts unless you have cascading deletes turned on (which I wouldn't do).
You'll also have failures when two teams are using the same DB in dev and deploying from their branches without merging them first.

Both of the above caveats can be avoided with the right processes in place.

Thoughts?

How do you do it? Does anyone out there have a better way?