Similar question, but for Oracle.
Why would I not always want to choose VARCHAR(MAX)?
+6
A:
Probably because Oracle does not support VARCHAR(MAX).
VARCHAR should not be used in Oracle at all.
As for now, it's a synonym for VARCHAR2, but it may change in future so that it will distinguish between an empty string and a NULL.
VARCHAR is supposed to do it but doesn't in current versions of Oracle, and hence should not be used.
In Oracle, maximum length for a VARCHAR2 is 4000 in SQL and 32767 in PL/SQL.
For larger values, you should use CLOB, but it's very different from a VARCHAR2. You should use special methods to access it, etc.
Quassnoi
2009-11-03 16:51:21
Since VARCHAR is ANSI SQL92 standart and VARCHAR2 is not, I don't agree "VARCHAR should not be used in Oracle at all." and "... but it may change in future ..." statements.
eyazici
2010-02-14 08:19:50
@eyazici: From documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3784 *To avoid possible changes in behavior, always use the `VARCHAR2` datatype to store variable-length character strings*. Disagreeing with the instruction manual is not a wisest thing to do, but of course it's your choice. If you want to build an `Oracle` application that may break on the next upgrade, using `VARCHAR` is definitely a way to go.
Quassnoi
2010-02-14 23:42:59
+6
A:
Because it doesn't work in Oracle! You could declare all columns as VARCHAR2(4000) if you wanted, but it isn't recommended by any means. There will be no difference in storage or performance of the database, but:
- you lose a constraint on the sensible values that can be stored in the column
- some client applications will allocate 4000 bytes of memory to receive data from the column when (say) 10 is all it will ever contain.
Tony Andrews
2009-11-03 16:51:55
Not just client applications, but PL/SQL too.Also, you want to consider what happens when you want to concatenate two columns together (eg to format an address line for an envelope).
Gary
2009-11-03 21:44:07