Why does VARCHAR need length specification?

Why do we always need to specify `VARCHAR(length)` instead of just `VARCHAR`? It is dynamic anyway.

UPD: I'm puzzled specifically by the fact that it is mandatory (e.g. in MySQL).

First off, it does not needed it in all databases. Look at [SQL Server][1], where it is optional.

Regardless, it defines a maximum size for the content of the field. Not a bad thing in itself, and it conveys meaning (for example - phone numbers, where you do not want international numbers in the field).


[1]:

[To see links please register here]

From [Wikipedia][1]:

> Varchar fields can be of any size up
> to the limit. The limit differs from
> types of databases, an Oracle 9i
> Database has a limit of 4000 bytes, a
> MySQL Database has a limit of 65,535
> bytes (for the entire row) and
> Microsoft SQL Server 2005 8000 bytes
> (unless varchar(max) is used, which
> has a maximum storage capacity of
> 2,147,483,648 bytes).


[1]:

[To see links please register here]

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 more the database knows about the data it is storing, the more optimisations it can make when searching/adding/updating data with requests.

The answer is you *don't* need to, it's optional.

It's there if you want to ensure that strings do not exceed a certain length.

You can see it as a constraint on your data. It ensures that you don't store data that violates your constraint. It is conceptionally similar to e.g. a check constraint on a integer column that ensure that only positive values are entered.

The most dangerous thing for programmers, as @DimaFomin pointed out in comments, is the **default length** enforced, if there is no length specified.

How SQL Server enforces the default length:

declare @v varchar = '123'
select @v

result:

1