XML Design in SQL Server 2005

Utilizing the XML data type in SQL Server 2005 can be a good way of creating a more flexible data model. Design of a SQL Server 2005 DB, however, should not discount the use of standard SQL data-type columns and the power of pure relational data. During the design phase, carefully think about what data SHOULD be in XML by identifying areas where the structure of the data could change frequently and/or drastically. Also assess whether particular pieces of data need to be strictly relational (belong in a standard SQL type column), or if they could benefit from a more flexible relational structure such as hierarchical XML elements (XML data type). Think about data placement and if something is a pivotal piece of data for the related system, such as whether it will be searched on often, or even retrieved in searches that will return larger data sets--these types of data are better candidates for standard SQL type columns.

Overall, the basic rule of thumb that I like to follow: if there's little reason to maintain a piece of data in a more flexible structure or relational structure, then don't put it in an XML data type.

In our next tip, we will explore using schema in SQL Server 2005 to define your data in order to more effectively hide the relational/XML split.

Permalink | Comments (0) | Technorati Tags : XML SQL