Storing Single Key/Value Settings

by data-centric on November 16, 2010

I read an interesting post today by Arnie Rowland about a simple but clever way to ensure only one row in a table used for application settings or config items.

I have found that tables like this can, if allowed to go unchecked, proliferate through a database which can cause minor, niggling maintenance issues and I can never remember where all the individual values are stored.

The solution I prefer to implement is a single ApplicationSetting table that holds all the individual settings using a key/value model. This means that there is just one place to look rather than having individual values scattered throughout various tables.
Such a table might look like this:

Notice how for both values, I code defensively in case the expected key/value pairs are not found.

One might think that something like this might lend itself to a scalar function but as noted a couple of years back by Simon Sabin among others, we need to be careful about the performance impacts of using scalar functions. Besides, the amount of SQL required to retrieve a value from the ApplicationSetting table and cast or convert it to the required data type isn’t much different to that required to call a UDF.