CODEWITHSUNDEEP
sqlsql-server-2008database-design
Tedderz
Tedderz

Reputation: 587

Should I Put An XML Blob Into A Separate Table?

I'm designing a transactional table which will have a lot of records. It will have a lot of reads and writes.

There will be one point at which the user uploads an XML file, and I store this in a database column of type XML.

For a given transactional record, this XML will not be needed as often as everything else. It will probably only get read a couple of times, and will usually just get inserted and not updated.

I'm wondering whether there is any advantage in storing this XML field in a separate table. Then, I can just join to it only when I need it. The only advantage that I perceive is that the individual records on the "main" table will take up less space. But, if my table is properly indexed, does that really even matter?

I suspect that I'm overthinking this and being premature with my optimization. Should I just leave the XML field on the main table?

One sample XML file I have is 12KB. I don't expect it to get much larger than that. I'm not sure if SQL Server's XML data type would store the information more efficiently than that.

To clarify, it is a one-to-one relationship. There will be one XML blob for every transaction. There won't be one XML blob for multiple transactions. And every transaction should eventually get an XML blob, even if it's not immediate.

Thanks, Tedderz

Upvotes: 3

Views: 1626

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56755

The answer is that you there is no need for you to modify or otherwise compromise your Logical data design to accommodate this Physical storage consideration.

This is because in SQL Server, XML is a "Large Value Type", and you can control whether these are Physically stored in-row or out-of-row through the use of the 'large value types out of row' option in the sp_tableoption system procedure, like so:

EXEC sys.sp_tableoption N'MyTable', 'large value types out of row', 'ON'

If you leave it OFF, then XML values less than 8000 bytes will be stored in-row. If you set it to ON, then all XML values (and [N]Varchar(MAX) columns) will be stored out of the table in a separate area. (This is all explained in detail here: http://technet.microsoft.com/en-us/library/ms189087(SQL.105).aspx)

The question of which to set it to is hard to say, but generally: if you expect to retrieve/modify this column a lot, the I'd recommend putting in in-row. Otherwise store it out of row.

Upvotes: 3

Steve Wellens
Steve Wellens

Reputation: 20640

No, you should not. If there is a one-to-one relationship, it belongs in the same table. Joins are expensive.

Upvotes: 0

marc_s
marc_s

Reputation: 755157

If your XML is rather large, and there are quite a few use cases where you don't need that information in your queries - then it could make sense to put it into a separate table - even if there's a 1:1 relationship in place.

The motivation here is this: if your "base" table is smaller, e.g. doesn't contain the XML blob, and you often query your table without needing to retrieve the XML, then this smaller row size can lead to much better performance on the base table (since more rows fit on a page, and thus SQL Server would need to load fewer pages to satisfy some of your queries).

Also: if that XML only exists in a small number of cases (e.g. only 10-20% of your rows actually have an XML blob), that might also be a factor that would work in favor of "outsourcing" the XML blob to a separate table.

Upvotes: 2

Related Questions