Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored.
But if VARCHAR is so efficient in handling the space management dynamically based on the content being stored, why databases has the support for CHAR datatype, all CHAR(n) fields can be VARCHAR(n) fields, right ?
You should read this article SQL varchar data type deep dive, and specifically the Storage and performance considerations using SQL varchar section:
Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.
Note this specifically speaks to the differences from a Microsoft SQL Server perspective, YMMV for other database systems, and not all support both CHAR and VARCHAR data types (e.g. SQLite has neither data type and only supports what it calls the TEXT data type).
And here's some good information, in similar vein, from MySQL's perspective, courtesy of RolandoMySQLDBA - DBA.StackExchange answer to What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?:
Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields.
Surprisingly, in the domain of PostgreSQL, it sounds like the same performance differences don't exist, and therefore the practical differences between the data types are less abundant. Please see this StackOverflow answer to Difference between text and varchar (character varying) and this DBA.StackExchange answer to Index performance for CHAR vs VARCHAR (Postgres):
...the performance of inserts and selects for all 4 data types are similar.
CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.
I would like to explain using a simple a example You need to Store gender in database Possible Values F,M,O using char it will take 1 byte for each entry (8bits) using varchar it will take 1 byte and 2 byte for each entry (say 24 bits)
by using char you would be able to reduce size of database to 33%
