varchar 值如何存储在 SQL Server 数据库中?

问题描述

我的程序员同事对他的团队领导提出了一个奇怪的要求；他坚持要创建长度为 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ⁿ.

这种限制有什么意义?

我可以假设短字符串(例如少于 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?

顺便说一句，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 数据都存储在行尾的可变长度部分(如果行不适合，则存储在 offrow 页面中).它在该部分消耗的空间量(以及它是否以行结束)完全取决于实际数据的长度，而不是列声明.

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 列将是 平均填充到其声明大小的 50%，因此在选择大小时这可能是一个更好的选择.

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 数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！