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.