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).
views:
115answers:
6
+2
A:
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).
Oded
2010-07-01 10:12:33
True, just be careful when not defining the size in SQL Server as it will default to different things depending on the scenario e.g. http://www.adathedev.co.uk/2010/04/sql-cast-to-varchar-without-size.html
AdaTheDev
2010-07-01 10:19:00
A:
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).
Luca Matteis
2010-07-01 10:13:11
+3
A:
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).
Robin Day
2010-07-01 10:16:27
Why doesn't VARCHAR by default mean VARCHAR(MAX)? INT has default size, so why couldn't VARCHAR have default size? What are the reasons behind this design decision taken when SQL standard was created?
Shooshpanchick
2010-07-02 08:08:26
VARCHAR(MAX) didn't exist when SQL Server standards were created. It was varchar(8000) or text.
Robin Day
2010-07-02 08:31:52
+1
A:
The more the database knows about the data it is storing, the more optimisations it can make when searching/adding/updating data with requests.
Tom Gullen
2010-07-01 10:17:35
What optimizations can it do based on varchar max length, for example?
Shooshpanchick
2010-07-01 11:12:52
A:
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.
SLC
2010-07-01 11:22:38
+2
A:
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.
a_horse_with_no_name
2010-07-01 22:26:03