CODEWITHSUNDEEP
sqlsql-servercasting
edosoft
edosoft

Reputation: 17281

Why is 30 the default length for VARCHAR when using CAST?

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?

Upvotes: 63

Views: 67818

Answers (6)

Joe R.
Joe R.

Reputation: 2052

My theory is that default 30 character length originated from the U.S. Postal Service specs for name and address lines:

http://pe.usps.gov/cpim/ftp/pubs/pub28/pub28.pdf

Upvotes: 4

curtisk
curtisk

Reputation: 20175

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.

Upvotes: 55

NG.
NG.

Reputation: 6063

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.

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

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)))

Upvotes: 1

Joe R.
Joe R.

Reputation: 2052

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.

Upvotes: 5

Charl
Charl

Reputation: 1012

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!

Upvotes: 33

xahtep
xahtep

Reputation: 1434

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.

Upvotes: 3

Related Questions