Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VARCHAR vs. CHAR

Posted on 2016-09-14
6
Medium Priority
?
127 Views
Last Modified: 2016-09-15
Can someone succinctly explain when it is best to use VARCHAR vs. CHAR?
0
Comment
Question by:burnedfaceless
  • 4
6 Comments
 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41797688
CHAR

Used to store character string value of fixed length.
The maximum no. of characters the data type can hold is 255 characters.
It's 50% faster than VARCHAR.
Uses static memory allocation.
VARCHAR

Used to store variable length alphanumeric data.
The maximum this data type can hold is up to
Pre-MySQL 5.0.3: 255 characters.
In MySQL 5.0.3+: 65,535 characters shared for the row.
It's slower than CHAR.
Uses dynamic memory allocation*.

Just to clear up... or sum up...

nchar and nvarchar can store Unicode characters.
char and varchar cannot store Unicode characters.
char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.
nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41797690
@Author - Please let me know if you need more on this ?
0
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41797693
char [ ( n ) ]

Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are charvarying or charactervarying.

Remarks

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store the character data.

If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar, we recommend the following:

Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes can be less than n characters.
Warning! Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation which counts against the 8,060 byte row limit during a sort operation. This can create an implicit limit to the number of non-null varchar(max) or nvarchar(max) columns that can be created in a table. No special error is provided when the table is created (beyond the usual warning that the maximum row size exceeds the allowed maximum of 8060 bytes) or at the time of data insertion. This large row size can cause errors (such as error 512) during some normal operations, such as a clustered index key update, or sorts of the full column set, which users cannot anticipate until performing an operation.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 7

Assisted Solution

by:D Patel
D Patel earned 1000 total points
ID: 41797710
Generally pick CHAR if all rows will have close to the same length. Pick VARCHAR when the length varies significantly. CHAR may also be a bit faster because all the rows are of the same length.

It varies by DB implementation, but generally VARCHAR uses one or two more bytes of storage (for length or termination) in addition to the actual data. So (assuming you are using a one byte character set) storing the word "FooBar"

CHAR(6) = 6 bytes (no overhead)
VARCHAR(10) = 8 bytes (2 bytes of overhead)
CHAR(10) = 10 bytes (4 bytes of overhead)
Bottom line is CHAR can be faster and more space efficient for data of relatively the same length (within two characters length difference).
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41797775
@Author - do you need any more help on this topic ?
0
 

Author Closing Comment

by:burnedfaceless
ID: 41800068
Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question