Relational Storage: Still the Default
|Figure 2. SQL Server 2005: SQL Server 2005 lets you expose natively stored XML in a tabular format.
The relational database isn't very sexy, but even after 25 years of commercial availability, it's still the best way to store structured data. Structured data has at least two characteristics:
- DenseEvery entity has roughly the same set of known properties.
- RegularEntities can reference each otherthink foreign keysbut in a predictable manner.
Here's a more intuitive test: If your data can easily fit into a spreadsheet, it's probably structured. Of course, the concept of structure in data exists on a continuum, so extremes are relatively rare. Even contact information can be sparsely populatednot everyone has a first name, a last name, and a middle initial, so these fields are occasionally NULLbut relational is usually the best way to store it. Here's some contact information in a tabular format:
||1234 American Way
||5432 American Way
Here's the XML equivalent:
<!--No family/given name pair-->
<Street>123 American Way</Street>
<Street>543 American Way</Street>
Each representation has its advantages: The table is more succinct; XML is better at dealing with Cher's single name. But overall, the table is the best way to represent this kind of data for a variety of reasons:
- Query performanceThe relational model is much simpler than XML, which makes searching easier and faster.
- Storage spaceXML is notoriously space-inefficientmaybe not a problem for a config file, but size can be an issue for large databases.
- LockingThe XML type is lockable like any other type. So, if many users are updating the same set of documents, they can run into decreasing performance and even deadlocks.
- LegacyA lot of technological infrastructure supports the relational model, such as reporting software and business intelligence systems.
So even if you import data in an XML format, it's typically best to shred it up and store it in a relational format. And this is where SQL Server 2000 is already pretty good. Listing 1
shows a XML file, shredded using SQL Server 2000's OPENXML rowset. Listing 2
shows a way to publish XML query results using functionality new in SQL Server 2005.