12

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.

| improve this question | |
  • 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 '11 at 14:15
  • How many extra fields are you talking about? And do they make sense to be part of the same Entity? – Andrew Bickerton Mar 31 '11 at 14:26
12

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.

| improve this answer | |
  • 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. – Matthew Steeples Mar 31 '11 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/… – Matthew Steeples Apr 1 '11 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 '11 at 12:44
5

  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.

| improve this answer | |
  • 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) – Matthew Steeples Mar 31 '11 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 '11 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. – Matthew Steeples Mar 31 '11 at 11:09
3

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

| improve this answer | |
-4

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.

| improve this answer | |
  • 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. – Andrew Bickerton Mar 31 '11 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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