varchar值如何存储在SQL Server数据库中？ [英] How are varchar values stored in a SQL Server database?

问题描述

我的同事程序员有一个奇怪的要求从他的团队领导;他坚持创建长度为16 * 2 ⁿ 的 varchar 列。

My fellow programmer has a strange requirement from his team leader; he insisted on creating varchar columns with a length of 16*2ⁿ.

这种限制的要点是什么？

What is the point of such restriction?

我可以假设短字符串（例如小于128个字符）直接存储在表的记录中，从这一点该限制将有助于对齐记录中的字段，较大的字符串存储在数据库heap中，只有对该字符串的引用保存在表记录中。

I can suppose that short strings (less than 128 chars for example) a stored directly in the record of the table and from this point of view the restriction will help to align fields in the record, larger strings are stored in the database "heap" and only the reference to this string is saved in the table record.

是吗？

这个要求有合理的背景吗？

Is this requirement has a reasonable background?

BTW，DBMS是SQL Server 2008。 >

BTW, the DBMS is SQL Server 2008.

推荐答案

就我所见，完全无意义的限制。假设标准 FixedVar 格式（而不是行/页压缩或稀疏列使用的格式），并假设你在谈论 varchar（1-8000 ）列

Completely pointless restriction as far as I can see. Assuming standard FixedVar format (as opposed to the formats used with row/page compression or sparse columns) and assuming you are talking about varchar(1-8000) columns

所有 varchar 数据存储在可变长度部分（或在离岸页面中，如果它不能适应行）。它在该部分中消耗的空间量（以及它是否以行结束）完全取决于实际数据的长度而不是列声明。

All varchar data is stored at the end of the row in a variable length section (or in offrow pages if it can't fit in row). The amount of space it consumes in that section (and whether or not it ends up off row) is entirely dependant upon the length of the actual data not the column declaration.

SQL Server将使用在分配内存时在列声明中声明的长度（例如对于 sort 操作）。在这种情况下假设 varchar 列将

SQL Server will use the length declared in the column declaration when allocating memory (e.g. for sort operations). The assumption it makes in that instance is that varchar columns will be filled to 50% of their declared size on average so this might be a better thing to look at when choosing a size.

这篇关于varchar值如何存储在SQL Server数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！