Member Avatar for Stefano Mtangoo

Can someone clarify me the difference between CHAR and VCHAR and which one is best to be default type in my all SQL queries?
Is it VCHAR or CHAR? :D

  • 3 Contributors
  • 5 Replies
  • 108 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

Short, practical guidance that builds on points from and : there isn’t a single “best” string type for every column — pick the type to match the data, indexing and character-set needs, and the storage engine behavior of your RDBMS.

Prefer variable-length string types for general text because they avoid wasting space for varying-length values; reserve fixed-length CHAR/NCHAR for truly fixed-size fields (small codes, fixed-format hashes when stored as raw bytes, etc.). For Unicode use the DB engine’s Unicode type (NVARCHAR/NCHAR in SQL Server or VARCHAR/CHAR with utf8mb4 in MySQL) so you don’t get surprise byte-length growth. SQL Server and MySQL docs cover these semantics in detail: see CHAR and VARCHAR (Transact-SQL) and MySQL CHAR and VARCHAR.

Watch these practical gotchas before choosing types: multi-byte character sets make storage larger than the character count implies; VARCHAR requires a length prefix (1–2 bytes in MySQL); very long VARCHARs can hit index-key-length limits or be stored off-page by InnoDB row formats; and fixed-length columns can reduce row fragmentation but aren’t a free performance win. See InnoDB row-format and index limits for details: InnoDB row format and InnoDB limits.

Practical checklist: choose the narrowest type that correctly represents the data; prefer native numeric/date types for numbers/dates; use native GUID types or compact binary storage instead of CHAR(36); test with your engine (EXPLAIN and real data) if performance matters. Use VARCHAR/NVARCHAR as the usual default, and opt into CHAR only when the fixed-size behavior is a clear, measured win. For UUIDs and other binary data, consider compact binary columns rather than text; see Percona’s UUID guidance for an example approach.

Recommended Answers

All 5 Replies

Member Avatar for ahmksssv

Can someone clarify me the difference between CHAR and VCHAR and which one is best to be default type in my all SQL queries?
Is it VCHAR or CHAR? :D

Varchar is the best option what i know...because char is fixed length...but varchar is automatically change size based on the input....If u use char, than memory wastage is more...

Member Avatar for nav33n

http://www.mssqlcity.com/FAQ/General/char_vs_varchar.htm

Summary: If you specify column's datatype as char(20) and you enter a value, say, "test", mysql still uses 20 bytes.. But if the column's datatype is varchar, and you enter "test", it uses only 4 bytes.. The best place to use char is when you are 100% sure that you save only a fixed length value, for example, a boolean "Y" or "N".

P.S. I am not too sure about the bytes used. This is just to give you a gist!

Member Avatar for nav33n

Varchar is the best option what i know...because char is fixed length...but varchar is automatically change size based on the input....If u use char, than memory wastage is more...

Ah! You beat me by 4 mins :twisted:

Member Avatar for ahmksssv

Ah! You beat me by 4 mins :twisted:

Hi Naveen,

Yah U r correct...ok is there any wrong with my answer..plz tell me...if any?

Member Avatar for nav33n

No. What you said is correct.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.