Why does casting text as varchar without specifying a length truncate the text at 30 characters?

Question

I was searching an MS-SQL Server 2005 database to make sure that a certain text field was always a multiple of 8 in length. When I ran the following query, I got that several rows were of length 30. But when I investigated those records, I found that they were longer than that.

select distinct len(cast(textfield as varchar)) from tablename

However, if I specify the length of the varchar as something longer(as in the following query), it works fine.

select distinct len(cast(textfield as varchar(1000))) from tablename

Anybody know why it defaults to 30 and if this is a configurable setting?

Answer 1

It's an arbitrary number, and as far as I know isn't configurable. I can't think of a case where you wouldn't want to specify the length.

Answer 2

http://stackoverflow.com/questions/359257/why-is-the-default-length-for-varchar-30-when-using-cast

Answer 3

Casting can happen when there are many different rows that need to fit that column. The database engine, because it isn't perfect, does not want to go to the trouble of checking the length of all possible rows a query might return to find out how long to set the maximum, so it doesn't check any rows - it just takes a default of 30 which is an arbitrary rough guess as it doesn't know better.

Arguably, it might have been smart for them to set the default to something like 256, or whatever the maximum length for VARCHAR is, but I guess more than anything, it's a helpful reminder that it really doesn't know how long you want it unless you tell it.