Using a Single Row configuration table in SQL Server database. Bad idea?

Question

I have done this two ways in the past - a single row table and a key/value pair table - and there are positives and negatives to each approach.

Single Row

• positive: the values are stored in the correct type
• positive: it is easier to deal with in code (due to the above)
• positive: default values can be given to each setting individually
• negative: a schema change is required to add a new setting
• negative: the table can become very wide if there are lots of settings

Key/Value Pair

• positive: adding new settings does not require a schema change
• positive: the table schema is narrow, with extra rows being used for new settings
• negative: each setting has the same default value (null/empty?)
• negative: everything has to be stored as strings (ie. nvarchar)
• negative: when dealing with the settings in code, you have to know what type a setting is and cast it

The single row option is by far the easiest one to work with. This is because you can store each setting in its correct type in the database and not have to store the types of the settings as well as their lookup keys in code.

One thing I was concerned with using this approach was having multiple rows in the "special" single row settings table. I overcame this by (in SQL Server):

• adding a new bit column with a default value of 0
• creating a check constraint to ensure that this column has a value of 0
• creating a unique constraint on the bit column

This means that only one row can exist in the table because the bit column has to have a value of 0, but there can only be one row with that value because of the unique constraint.

You should create a table with a column for the information type and information value (at least). This way you avoid having to create new columns every time a new information is added.

A single row will work fine; it will even have strong types:

show_borders    bit
admin_name      varchar(50)
max_users       int

One disadvantage is that it requires a schema change (alter table) to add a new setting. One alternative is normalizing, where you end up with a table like:

pref_name       varchar(50) primary key
pref_value      varchar(50) 

This has weak types (everything is a varchar), but adding a new setting is just adding a row, something you can do with just database write access.

Personally, I would store it in a single row if that is what works. Overkill to store it in an SQL table? probably, but there is no real harm in doing so.