[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL - what is difference between char (15) and varchar(15)

Posted on 2012-09-16
Medium Priority
Last Modified: 2012-09-16
Can one explain the difference
Question by:tommym121

Assisted Solution

kiwistag earned 1000 total points
ID: 38403781
Effectively varchar(x) can be a value UP to x whereas char(x) pads the table entry to be a total of x - therefore varchar is more efficient whereas char is an exact amount.

From http://dev.mysql.com/doc/refman/5.0/en/char.html:
 The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section E.7.4, “Table Column-Count and Row-Size Limits”.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
LVL 18

Accepted Solution

Jerry Miller earned 1000 total points
ID: 38403978
In MS SQL, I only use CHAR data types when I know for a fact that the data is a set length will not change in the future. If there is a sliver of doubt as to whether it will change, I use VARCHAR.

When querying the database, using CHAR data types make it a little harder to compare strings since you have to TRIM all of you data as it comes from the table to make sure that you are removing all trailing spaces. If you forget in one query, you will get unexpected results.

Author Closing Comment

ID: 38404178

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

873 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