When to use XML data type

I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the database.

As far as I can see I have 3 options:

• Add a column in the table for each extra value

• Add a linked table which references the original table and has records only where we need to store a value

• Use the XML data type in the original table and store all of the values in this.

Are there any other options that I've not considered?

I'm trying work out the pros and cons of each method. As far as I can tell 1 would be the easiest and 2 would take the least amount of space but I'm struggling to find many resources for 3.

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario.


The SQL Server Database Engine uses
the SPARSE keyword in a column
definition to optimize the storage of
values in that column. Therefore, when
the column value is NULL for any row
in the table, the value requires no
storage.

I can't imagine an XML solution performing well in this scenario, it will have a huge overhead of redundant metadata and will be slow to query.

StackExchange Database Administrators Q#2009, answer score: 12