I understand the difference between char and varchar but I'm not sure of exact meaning of varchar maximum length.
When we store string whose length is less than 10, is there any difference between varchar(10) and varchar(100)?
I think both cases use same space and have same performance. If so, why do we need varchar max limit?
Is it enough to use just "varchar" instead of "varchar(xxx)"?
(added) I'm using MySQL 5.0.67
This depends entirely on the DBMS engine being used. SQL itself does not mandate how things are stored physically, just how they're seen logically.
For example, your DBMS may allocate space in the row for the maximum size, plus some extra bytes to store the length. In that case, there would be a big difference between varchar(10) and varchar(1000) since you would waste quite a bit of space per row.
Alternatively, it may use a buffer pool for the varchar data and store only the length and the buffer pool "starting address" in the row. In that case, every single row would store identically-sized information for a varchar column regardless of its size, but there would be an added step to extract the actual data in that column (following the link to the buffer pool).
The reason you use a varchar is exactly why it's named varchar. It allows you to store variable-sized data elements. Typically, char(10) gives you ten characters, no matter what, padding it with spaces if you insert something shorter. You can trim trailing spaces off as you extract it but that won't work so well if the data you want to store is actually "hello ", with a trailing space you want preserved.
A decent DBMS engine may decide to make a trade-off depending on the maximum size of the varchar column. For short ones, it could just store it inline in the row and consume the extra bytes for the size.
Longer varchar columns could be "outsourced" to a separate buffer pool to ensure row-reading is kept efficient (at least until you need the large varchar column, anyway).
What you need to do is re-ask the question for your specific DBMS so as to get a more targeted answer.
Or, in all honesty, engineer your database to only store the maximum size. If you know it's 10, then varchar(1000) is a waste. If, in the future, you need to enlarge the column, that is the time to do it, rather than now (see YAGNI).
For MySQL, you'll want to look at Chapter 14 Storage Engines of the online documentation.
It covers the various storage engines (such as InnoDB and MyISAM) that MySQL uses and, looking deep enough, you can see how the information is physically stored.
For example, in MyISAM, the presence of variable length data in a table (varchar included) usually means dynamic tables. This follows a scheme roughly analogous to the buffer pool concept I mentioned above, with the advantage that less space is wasted for variable sized columns, and the disadvantage that rows may become fragmented.
The other storage format (discounting compressed format since it's only really used for read-only tables) is the static one, where data is stored in a single physical row.
Information on the InnoDB physical structures can be found here. Depending on whether you use the Antelope or Barracuda file format, you end up with the "all information is a physical row" or "buffer pool" situation, similar to the MyISAM distinction between dynamic and static.
In SQL Server, the limit does not affect how the data is stored on disk. What it does provide, though, is one constraint for free. If you, as the database designer, only want up to 10 characters stored, you've prevented someone from storing a novel instead.
Is it enough to use just "varchar"
Again, for SQL Server, almost certainly not what you want. In most circumstances, if you don't specify a limit, you get a varchar(1) (surely the most pointless data type ever conceived). Occasionally, it's a varchar(30).
In Oracle, the Varchar stretches in size depending on its use, just up to the point where you set the limit. This means that indeed, a varchar(10) and a varchar(100) containing 2 characters use the same space (which is different for a char, which always uses the full allocated space).
