SQL Server 2012 and different datatypes stored in one place

Question

Okay, I am not a database architect, so this question is completely coming from a software developers point of view. I am currently working to get a simple settings database and was wondering what is the most efficient, performant way to store different types in SQL server. For example, SettingA can have a boolean, int, float, or string value. What is the most efficient way to store that data and the most performant to retrieve.

1. Store all types in one field that is a string and have the application server convert it to it's proper value.

2. Have different fields for different types and have the application server see which field is not null and use it (BooleanValue, StringValue, IntValue, FloatValue and have only one be non-null and with a value)

3. Normalize the settings into different tables based on types and only add value to the table if that type is used. (Four tables BooleanValues, StringValues, IntValues, FloatValues with a foreign key to Settings table). SP would then return whichever table has a record.

Performance is definitely my #1 concern, but space concerns would be a close second.

Answer 1

Store the settings in an XML column.

With that I mean all settings for an item in a single XML settings document, so that they can be retrieved and updated in one go. Provide a schema to reduce the storage costs and optimize queries. The database will then know the data types of the setting values.

If you wish to query for all items with a specific value for a setting, you can use XML indices and/or XQuery queries.

I've created a SQLFiddle to show what this could look like: http://sqlfiddle.com/#!6/49c34/1

As for the indexing, take a look at XML Indexes and, in particular, Selective XML Indexes:

The selective XML index feature lets you promote only certain paths from the XML documents to index. At index creation time, these paths are evaluated, and the nodes that they point to are shredded and stored inside a relational table in SQL Server. This feature uses an efficient mapping algorithm developed by Microsoft Research in collaboration with the SQL Server product team. This algorithm maps the XML nodes to a single relational table, and achieves exceptional performance while requiring only modest storage space.

I'd expect this to behave great, better even than using a table row per setting, if the settings are usually inserted and retrieved together. In that case it makes no sense to rip them into bits, scatter the bits into table rows, only to retrieve and glue them back again when you retrieve them.

If you wish to query for a couple specific settings, or criteria for these specific settings, selective XML indexes will help you achieve this.

When to beware:

• If settings differ wildly between items. For they will not have a shared schema and the storage needed for the settings document becomes big, possibly too big.
• If you require a wide range of queries on different elements of the settings document to perform well. For you'll probably end up needing a lot of indexing space.
• If part of the settings change more often than the rest, you may want to break those out and store them separately. For else you risk updating the entire settings document each time one single bit gets twiddled.