• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 23132
  • Last Modified:

maximum size of VARCHAR2()

What is the maximum size of VARCHAR2(),
Is it 3000?
If yes,
If I create table with VARCHAR2(3000) is it wasting the memory or it will cover only what space I used?


For example,

         Create table x (name VARCHAR2(3000));

 and

          Insert into x (name) values (‘SQL’);

I used only 3, Does this waste 2997 or not?
0
javamate06
Asked:
javamate06
1 Solution
 
Harisha M GCommented:
Hi javamate06, in which database ? Oracle ?

If you use VARCHAR2, it won't waste any space.

http://www.ss64.com/orasyntax/datatypes.html


---
Harish
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In Oracle: 4000
In MSSQL (2000): 8000
in MSSQL (2005):  MAX = 2GB

In all the databases, there is NO waste of space.
0
 
cdemirCommented:
in Oracle;

>> What is the maximum size of VARCHAR2(),
varchar2(4000)

>> I used only 3, Does this waste 2997 or not?
No. Allowing extra space in a VARCHAR column is usually harmless — after all, the definition doesn’t affect the storage — but it does affect sorts.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
javamate06Author Commented:
thanks mgh_mgharish

You said "If you use VARCHAR2, it won't waste any space.". So, is there a type like VARCHAR2 wasiting spaces by initializing the size?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, using CHAR(4000) will allocate each time 4000 characters
0
 
Harisha M GCommented:
angel is correct..

>> but it does affect sorts.
Performence can be increased slightly by creating index on that column
0
 
LowfatspreadCommented:
Hi javamate06,
performance is also affected by having to store the length of the data with the column...
but that's not usually a concern if your storing a lot of data...

a general rules of thumb from db2/udb
if its less than 30 bytes define it as char to its maximum value
if it generally varies by less than 10-20 bytes use char


hth
 


Cheers!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now