Why does VARCHAR need length specification?

Why do we always need to specify VARCHAR(length) inste开发者_Python百科ad of just VARCHAR? It is dynamic anyway.

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

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

First off, it does not needed it in all databases. Look at SQL Server, 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).

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

From Wikipedia:

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

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