76

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

Fixpoint
  • 9,619
  • 17
  • 59
  • 78

7 Answers7

44

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
  • 100,552
  • 23
  • 116
  • 167
  • 13
    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? – Fixpoint Jul 02 '10 at 08:08
  • 2
    VARCHAR(MAX) didn't exist when SQL Server standards were created. It was varchar(8000) or text. – Robin Day Jul 02 '10 at 08:31
  • 4
    VARCHAR(MAX) is handled differently than VARCHAR(8000) (the largest non-max type) under the hood. Please only use VARCHAR(MAX) when you really need it or your performance will suffer. https://dba.stackexchange.com/questions/173895/understanding-varcharmax-8000-column-and-why-i-can-store-more-than-8000-charac – bobroxsox Sep 18 '17 at 20:55
18

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
  • 489,969
  • 99
  • 883
  • 1,009
  • 3
    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 Jul 01 '10 at 10:19
  • 2
    "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." from the link mentioned – Dima Fomin Jan 31 '18 at 12:45
7

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.

4

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.

NibblyPig
  • 51,118
  • 72
  • 200
  • 356
3

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
  • 29,161
  • 19
  • 114
  • 169
3

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
  • 61,249
  • 84
  • 283
  • 456
  • 17
    What optimizations can it do based on varchar max length, for example? – Fixpoint Jul 01 '10 at 11:12
  • 1
    Numerous - for example, before a query can execute it must be allocated RAM for working space, sorting, hashing etc; this has to be known up-front in most DBMS and the engine will make an educated guess what this should be - it bases this in part by knowning how much data is expected to be returned by the query, which it knows by the data types of columns. Specify varchar(max) for everything and, for example, SQL Server assume an average ~50% fill rate and attempt to reserve way too much memory, impacting the overall performance of the server and concurrency. – Stu May 03 '22 at 20:19
3

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
themefield
  • 3,847
  • 30
  • 32