CODEWITHSUNDEEP
database-designdata-modeling
robinmag
robinmag

Reputation: 18110

performance between varchar(1) vs char(1)

Is there any performance difference between varchar(1) and char(1)? Which RDBMS handle these two datatypes and sizes in different ways?

Upvotes: 14

Views: 26615

Answers (6)

Awk Sod
Awk Sod

Reputation: 161

In MS SQL VARCHAR(1) uses three bytes of storage and CHAR(1) uses one byte of storage.

CHAR(1) is more efficient than VARCHAR(1) in processing and storage.

The breakeven point on VARCHAR would be anything greater than 3 characters if using variable length data.

If using fixed length then CHAR is always more efficient by two bytes.

REF: http://msdn.microsoft.com/en-gb/library/ms176089.aspx

Upvotes: 16

Saghir
Saghir

Reputation: 2435

For Oracle

The VARCHAR2(1) and CHAR(1) are identical in all aspects. There is no compelling reason to use the CHAR type in that case, and to avoid any confusion, I “just say no,” even for the CHAR(1) field.

Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

Upvotes: 3

Madison Knight
Madison Knight

Reputation: 364

My understanding after reading MySQL's description of VARCHAR and CHAR is that if you're data is always going to be the same length, then CHAR could potentially save you a single byte of data because VARCHAR requires the storage of a byte separate from the actual data in order to record how long the data is. However, if you're data isn't a constant length and you're thinking of space considerations, it would make sense to use VARCHAR because CHAR will always use a byte for every bit of the declared length whether there is anything in that space.

In the end, though, it probably doesn't make that big of a difference as the amount of data involved is quite small.

Here's where I got my information: http://dev.mysql.com/doc/refman/5.0/en/char.html If you follow the link, pay special attention to the little chart as it gives examples of how the data would be handled with VARCHAR vs. CHAR.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

The difference will be negligible in most cases. Concentrate your performance oriented design efforts in places where it will make a real difference, like table composition and index design.

It helps to divide the design effort into two layers: logical design and physical design.
Most of the performance oriented effort is in the second stage, physical design.

In logical design, flexibility trumps performance. Except when it doesn't.

Upvotes: 9

Gary Myers
Gary Myers

Reputation: 35401

As far as Oracle is concerned....

CHAR(2) will always use two bytes/characters of storage. VARCHAR2(2) may only use one. So the general case is to use VARCHAR2 instead of CHAR.

In practice you shouldn't see a difference performance wise for a one character column.

Since there's never a never a benefit from CHAR, I always use VARCHAR2.

Upvotes: 2

SoftwareGeek
SoftwareGeek

Reputation: 15772

it hardly matters, since you are using 1 byte in both cases.

but long story short, varchar is variable size & char is fixed size. in large quantities this can make a difference in storage space as well as affect computation time.

performance wise, use char if data is going to be fixed length. if you are flexible with varying length of your data then use varchar.

Upvotes: 0

Related Questions