SQL 为什么使用 CAST 时 VARCHAR 的默认长度是 30？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/359257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is 30 the default length for VARCHAR when using CAST?
提问by edosoft
In SQL server 2005 this query
在 SQL Server 2005 中这个查询
select len(cast('the quick brown fox jumped over the lazy dog' as varchar))
returns 30 as length while the supplied string has more characters. This seems to be the default. Why 30, and not 32 or any other power of 2?
返回 30 作为长度，而提供的字符串有更多字符。这似乎是默认设置。为什么是 30，而不是 32 或任何其他 2 的幂？
[EDIT]I am aware that I should always specifiy the length when casting to varchar but this was a quick let's-check-something query. Questions remains, why 30?
[编辑]我知道在转换为 varchar 时我应该总是指定长度，但这是一个快速的让我们检查一下查询。问题仍然存在，为什么是 30？
采纳答案by curtisk
Why don't you specify the varchar length? ie:
为什么不指定 varchar 长度？IE：
SELECT CAST('the quick brown fox jumped over the lazy dog' AS VARCHAR(45))
As far as why 30, that's the default length in SQL Server for that type.
至于为什么是 30，那是 SQL Server 中该类型的默认长度。
From char and varchar (Transact-SQL):
从char 和 varchar (Transact-SQL)：
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
当在数据定义或变量声明语句中未指定 n 时，默认长度为 1。当使用 CAST 和 CONVERT 函数时未指定 n 时，默认长度为 30。
回答by Charl
On your question as to why 30 and not 32 or any other power of 2, the storage size is n + 2 bytes for varchar(n), which makes the byte storage size 32 for a string of length 30. Might be that this is what they looked at?
关于为什么是 30 而不是 32 或任何其他 2 的幂的问题，varchar(n) 的存储大小为 n + 2 个字节，这使得长度为 30 的字符串的字节存储大小为 32。可能是这样他们看了什么？
Then just a point of clarity on some of the comments: The default length for an unspecified length varchar field is n=1. The default string length that CAST or CONVERT returns for a conversion of this data type is 30.
然后对一些评论进行澄清：未指定长度的 varchar 字段的默认长度为 n=1。CAST 或 CONVERT 为这种数据类型的转换返回的默认字符串长度是 30。
Very cool question!
很酷的问题！
回答by Frank R.
My theory is that default 30 character length originated from the U.S. Postal Service specs for name and address lines:
我的理论是，默认的 30 个字符长度源自美国邮政服务对姓名和地址行的规范：
回答by Frank R.
Microsoft chose 30 as the default length for CHAR and VARCHAR in SQL Server, Access' Jet DB engine and several other of their products. It originates from the old days when a name or address column's default length was initially set to 30. Other DB's like Informix default to 20 for CHAR and 255 for VARCHAR.
Microsoft 选择 30 作为 SQL Server、Access 的 Jet DB 引擎和其他几个产品中 CHAR 和 VARCHAR 的默认长度。它起源于过去，名称或地址列的默认长度最初设置为 30。其他数据库（如 Informix）默认为 20 CHAR 和 255 VARCHAR。
回答by xahtep
I don't know why they chose 30, but it was the same in Sybase SQL Server, which Microsoft's SQL Server was developed from. It seems to be a peculiarity of those RDBMSs as it's not in the SQL standards, and other servers differ in their behaviour.
我不知道他们为什么选择 30，但它在 Sybase SQL Server 中是一样的，微软的 SQL Server 就是从它开发出来的。这似乎是那些 RDBMS 的一个特点，因为它不在 SQL 标准中，而其他服务器的行为也有所不同。
回答by NG.
Default size with convert/cast has nothing to do with the memory allocation and hence the default value (ie 30) is not related to any power of 2.
转换/转换的默认大小与内存分配无关，因此默认值（即 30）与任何 2 的幂无关。
regarding why 30, this is microsoft's guideline which gives this default value so as to cover the basic data in first 30 characters. http://msdn.microsoft.com/en-us/library/ms176089.aspx
关于为什么是 30，这是微软的指导方针，它给出了这个默认值，以便在前 30 个字符中覆盖基本数据。 http://msdn.microsoft.com/en-us/library/ms176089.aspx
Although one can always alter the length during conversion/cast process
虽然在转换/铸造过程中总是可以改变长度
select len(cast('the quick brown fox jumped over the lazy dog' as varchar(max)))
