Menu
In SQL server 2005 this query
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?
[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?
848
VARCHAR is a variable-length character data type. The default length is 80, and the maximum length is 65000 octets. For string values longer than 65000, use Long Data Types. Values can include trailing spaces.
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters. The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255.
The "length" of the VARCHAR is not the length of the contents, it is the maximum length of the contents. The max length of a VARCHAR is not dynamic, it is fixed and therefore has to be specified. If you don't want to define a maximum size for it then use VARCHAR(MAX).
The size of the maximum size (m) parameter of a VARCHAR column can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store character strings that are shorter, but not longer, than the m value that you specify.
Why don't you specify the varchar length? 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.
From char and 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.
92
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?
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.
Very cool question!
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
