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:

  1. Add a column in the table for each extra value
  2. Add a linked table which references the original table and has records only where we need to store a value
  3. 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.

  • 1
    To add a personal rant against xml abuse in a database I'd answer directly to the question in the title and say a big fat: NEVER! For the actual body of the question I'll let the colleagues help you, because you already have very good answers :-). PS: you can actually ignore my first sentence.
    – Marian
    Mar 31, 2011 at 14:15
  • How many extra fields are you talking about? And do they make sense to be part of the same Entity? Mar 31, 2011 at 14:26

4 Answers 4


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.

  • 1
    I think sparse columns are what I'm after. I'm expecting a very small amount of data to be stored in probably a handful of columns on certain tables. Mar 31, 2011 at 16:08
  • I'm not sure if I' reading this right, but according to this link sparse columns are basically a database implementation of what I was looking at for 3 anyway aren't they? blog.sqlauthority.com/2008/07/14/… Apr 1, 2011 at 9:48
  • If it is internally implemented like that (and I don't know that it is, that's just someone's blog) then you will never have to deal with or parse the XML yourself - it will behave exactly as a regular table with (with any restrictions on datatypes)
    – Gaius
    Apr 1, 2011 at 12:44
  1. A nullable column takes no space if variable length in SQL Server. The fact of being NULL is stored in the NULL bitmap. You can index it if required with filtered indexes so you ignore NULL columns.

  2. Adds complexity when you consider point 1.

  3. Don't. Hard to search, parse etc: you will regret this later

It also depends on size: will this be char(1000) for a few billion rows? Or tinyint for 100k rows? If the latter consider the added complexity of point 2: not worth it.

  • Do you have a reference that a nullable column that is null takes no space. I was aware that whether it was null or not was stored in the null bitmap but thought for fixed length fields that the data was still stored in the table. The datatype I'll be using for most of these values is money (so 8 bytes) Mar 31, 2011 at 9:45
  • 1
    @Matthew Steeples: I said variable length takes no space already. And for reference sqlskills.com/BLOGS/PAUL/category/On-Disk-Structures.aspx#p41 How may rows for these 8 bytes?
    – gbn
    Mar 31, 2011 at 10:41
  • At the moment we're at 500,000 rows but we're going to be expanding (hopefully) at a rate of about 1 million per weekday once we're properly live. Mar 31, 2011 at 11:09

With SQL Server 2008 you have the additional option of using sparse columns, which are designed specifically for the situation that you mentioned.

They have the added benefit that you can view them as a combined XML object using XML COLUMN_SET or reference them individually and they provide a tremendous space savings.

Check out the following blog article for more details: http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-Sparse-columns-and-XML-COLUMN_SET.aspx


A fourth option: don't use tables. Tables are very badly suited to this kind of data (in fact, to any kind of data that hasn't been force-fitted into tabular form). Just use XML.

  • 3
    -1 as while it is true that "don't use tables" is an option, the answer is clearly stating a rant against table structures and not actually submitting a helpful answer. Mar 31, 2011 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.