varchar(max)列的开销对于小数据是多少？

Question

作为从外部数据源批量加载数据的一部分，暂存表被定义为varchar(max)类型的列。其想法是每个列都能够存储找到的源CSV文件中的任何内容，并且我们稍后会对数据进行验证（例如类型、大小、精度等）。

但我担心varchar(max)列存在过多开销，尤其是当列长度小于200个字符时。设计此方案的人保证这是ETL最佳实践，但我想与社区验证这种说法。

- Ralph Shillington

Answer 1

VARCHAR(MAX)列的值将根据可用空间保存在表行中。因此，如果您只有一个VARCHAR(MAX)字段且其大小为200或300字节，则很可能与其余数据一起内联存储，这里不会产生问题或额外开销。

只有当单个行的所有数据无法再适合于单个SQL Server页面（8K）时，SQL Server才会将VARCHAR(MAX)数据移动到溢出页面中。

总的来说，我认为您在两个世界中得到了最好的结果 - 在可能的情况下进行内联存储，在必要时进行溢出存储。

Marc

附注：正如Mitch所指出的那样，可以关闭此默认行为 - 然而，我没有看到任何强制执行此操作的充分理由....

- marc_s

---

我有一个varchar(max)列，它总是为空的。它只是出于遗留原因存在（并使我的旧ASP.net代码无需更改即可工作）。所以如果我理解正确，如果我将其删除，我的数据库不会变得更小（如果有变小的话）。对吗？ - Tillito

根据Jeff Hall的回答，这取决于行数。假设有100万行，我会节省2 MB。正确吗？ - Tillito

Answer 2

varchar(n)和varchar(max)之间的存储开销相同

存储大小是输入数据的实际长度+ 2个字节

MSDN参考

查看这些类似的SO问题：

https://stackoverflow.com/questions/166371/varcharmax-versus-varcharn-in-ms-sql-server 总是使用nvarchar（MAX）有什么缺点吗？

- Jeff Hall

Answer 3

据我所知，您可能正在考虑的开销（以与SQL Server中存储TEXT或BINARY值相同的方式将数据存储为行外数据）仅适用于数据大小超过8000字节的情况。因此，在ETL过程中使用较小的列不应该成为问题。

- Steve Willcock

---

根据表选项“大型值类型超出行”的设置而定，如果设置为“ON”，它将使用16字节指针来存储表外的数据。 - Mitch Wheat

Mitch：你会选择打开这个选项吗？在我看来，这样做似乎会失去MAX类型的所有优势，不是吗？ - marc_s

Answer 4

如果您在MSSQL2005中使用varchar(max)或varbinary(max)，SSIS会为记录中的每个列创建一个临时文件，这可能会降低性能并成为一个大问题。微软声称他们在MSSQL2008中解决了这个问题。

- JSC

---

这听起来非常奇怪 - 你必须用一些参考资料来支持它 - 比如一个指向微软“声明”的链接。 - Ralph Shillington

我亲眼所见。我们有一个带有16个核心和64 GB内存的SSIS服务器，当我们使用varchar(max)/varbinary(max)创建超过大约800万条记录时，需要超过12个小时才能全部创建。但是如果我们使用varchar(8000)/varchar(8000)，那么几分钟之内就可以准备好。我会尝试找到这个声明，在网上看到过。 - JSC

这不是确切的事情，但微软表示他们改进了创建临时文件的分配。http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=346675 - JSC

Answer 5

我想说的是，开销不应该太大，因为我认为 SQL 并没有自动分配 nvarchar 的数据量，而只会为插入的内容分配所需的空间，但我没有任何证据来证明或支持这个想法。

- DForck42