Microsoft SQL Server 2005/2008:XML与text / varchar数据类型 [英] Microsoft SQL Server 2005/2008: XML vs text/varchar data type
问题描述
以XML类型而不是text / varchar / ntext存储XML更有意义(除了服务器端验证XML / schema / dtd)?我不打算在数据库端做任何XML操作。
Does it make more sense (except of server side validation XML/schema/dtd) to store XML in XML type instead of text/varchar/ntext? I'm not planning to do any XML manipulation on database side.
我的调查的目的是减少数据库大小。为此,我可以对无类型的XML使用 XML数据类型吗?
The purpose of my investigation is to decrease database size. Can I use XML data type for untyped XML for this purpose? What are the pros and cons?
我发现了一个与主题相关的文章,但我不确定作者的假设/结论是否正确。
I found an article related to the topic, but I am not sure if the authors assumptions/conclusions are correct.
推荐答案
我的快速调查显示,MS SQL 2005(快速版)
My quick investigation shows that MS SQL 2005 (Express Edition)
Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86)
Aug 5 2008 12:31:12
版权所有(c)1988-2005 Microsoft Corporation
Windows NT 6.0上的Express Edition(Build 6000:)
Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6000: )
以大约70%的开销存储XML(可能更快的处理/解析)。
store XML with overhead about 70% (possible for faster processing/parsing).
我的数据转换前:rows = 160320,reserved = 178576 KB,data = 178184 KB,index_size = 272 KB,unused = 120 KB
My data before conversion: rows=160320, reserved=178576 KB, data=178184 KB, index_size=272 KB, unused=120 KB
转换后的数据:rows = 160320 ,reserved = 309702 KB,data = 307216 KB,index_size = 1672 KB,unused = 184 KB
My data after the conversion: rows=160320, reserved=309702 KB, data=307216 KB, index_size=1672 KB, unused=184 KB
因此, XML数据类型,如果您不打算在数据库端使用XML技术。
这篇关于Microsoft SQL Server 2005/2008:XML与text / varchar数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!