To Store XML or To Not Store XML

In SQL Server 2005, XML was a new data type introduced in order to store XML documents and fragments in a SQL Server database. While this data type is most definitely useful, one of the main questions that you need to ask yourself before using it is "Should I store this data as XML?"
Based on what data should be stored and/or the processing that the data will participate in later, the shredding the XML data to store the data in a relational form (if it is possible) may be the better than storing it as XML to avoid possible storage and performance issues in the future. If the data is going to be used as XML for later processing then storing it as XML is beneficial to avoid extra processing once you consider the cost for storage as being minimal compared to the processing cost. If you are unsure of when or why to use the XML data type then I suggest you read Tim Anderson's interview with Dr Micheal Rys which highlights the thinking behind some features that were introduced and provides some guidance on how to use them. Please note that this article is based on SQL Server 2005, new and improved XML features for SQL Server 2008 will appear in a later post.

Continue Reading →

Data Types

As stated on MSDN, a data type is an attribute that specifies the type of data that the object can hold. Choosing an appropriate data type is one of the most important decisions that you will make when designing your database. Your decision can have a positive or negative impact on your database's performance and storage requirements.

If the data type is too restrictive it can cause an issue like restricting the growth of your database. For example using the 'tiny int' data type which has a maximum value of 255 instead of using the 'int' data type which has a maximum value of 2,147,483,647. If you choose a data type that is broad and allows anything to be stored then you may end up using excess server resources such as disk space and memory which will result in performance issues.

SQL Server 2005 and 2008 native data types are organized into seven categories: Exact numerics, Approximate numerics, Date and Time, Character strings, Unicode character strings, Binary strings and other data types (also called Special purpose). Some books include Unicode character strings as part of Character strings category and highlights Monetary as the 7th category. Check MSDN: Data Types for more detailed information about the categories and their data types, including storage details and restrictions. Ensure that you avoid using data types that are being deprecated to avoid future upgrade issues.

Continue Reading →