I want to save some serialized data in a SQL Server 2005 table. What is the appropriate field type for this? I won't be associating a schema with the data. The data could vary in length--even up to several MB. I'm not sure whether to use text, NVARChar(MAX) or XML.
Brian
Brian,
A couple of SQL Server 2005 MSDN aritcles talk about when to use XML and some of the best practices. Some relevant ones are "XML Best Practices for Microsoft SQL Server 2005"; "XML Options in Microsoft SQL Server 2005"; "Performance Optimizations for the XML Data Type in SQL Server 2005".
In short XML is very useful 1) when you want schema validation (may not apply to your case); 2) when you want to query into the XML data or update granular parts of it. If your data is in XML format but your application merely uses the database to store and retrieve the data, an (n)varchar (max) column might suffice.
As a side note: Use varchar and nvarchar datatypes instead of text and ntext as the latter are in deprecation path (Deprecated Database Engine Features in SQL Server 2005)
Thanks
Babu
No comments:
Post a Comment