473,549 Members | 2,251 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

char vs. varchar

Greetings,

I have a question. I work on some SQL2k/ASP.NET apps at work. My
predacessor, who created the databases/tables seemed to have liked to
use 'char' for all text fields. Is there a reason why he would have
done this over using varchar? It's a minor annoyance to always have to
RTRIM data and it makes directly making changes to the database more
annoying (with all the pointless trailing spaces)?

I usually use char for fixed string lengths, like state abbreviations
or something, and varchar for strings of unknown length.

Is it a performance issue? Our database doesn't do much traffic, for
the most part.

Jul 23 '05 #1
5 29480
It's not a performance issue unless you're using varchar(1) and the
overhead that incurs and have millions of records and higher traffic
than you probably have.

As a matter of fact, for larger char() fields, they can be slower than
varchar(), because it has to physically store more data pages than if
you used varchar(). If there are more data pages for the same number
of records, things get slower.

Make life easy on yourself and use varchar(). Don't use varchar(1)
though. I have seen people use it.

Jul 23 '05 #2
The difference between char and varchar are in both storage and performance:

1. Storage wise: char columns have fixed length. If the user supplied value
for the column is less than the fixed length defined in the schema, the
column is padded with 0 at end to make the total length fixed. varchar
doesn't have a fixed length thus no padding is needed. But as the result
varchar columns have to store the size of the data together with the column
data, which takes an extra 2 bytes per varchar column.

2. Performance wise locating char is a little faster than varchar. Since
char columns have fixed length, they are stored in fixed location in a row.
This means locating a char column can directly jump to the fixed location in
a row to read. For varchar column since the size of the data is variable,
they can't be stored in fixed location in a row and rather there is soem
kind of lookup table in the row format to store the location of each varchar
column. This means locating a varchar column has to lookup the location of
the column in the lookup table stored in the row first before jumping to the
location to read. Referencing the lokup table introduces some perofrmance
overhead, especially ifthe lookup table reference causes cache line miss.

In summary, it is a matter of trade-off between padding+faster locate and
2-bytes-overhead-per-column+slower locate when choosing char v.s. varchar.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<dm*********@de spammed.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
Greetings,

I have a question. I work on some SQL2k/ASP.NET apps at work. My
predacessor, who created the databases/tables seemed to have liked to
use 'char' for all text fields. Is there a reason why he would have
done this over using varchar? It's a minor annoyance to always have to
RTRIM data and it makes directly making changes to the database more
annoying (with all the pointless trailing spaces)?

I usually use char for fixed string lengths, like state abbreviations
or something, and varchar for strings of unknown length.

Is it a performance issue? Our database doesn't do much traffic, for
the most part.

Jul 23 '05 #3
You would never see a practical performance advantage in using char
over varchar, unless you had an extremely high transaction application.
The varchar offset lookup is optimized in-memory. The real bottleneck
is disk I/O, not a few extra CPU cycles from looking up varchar
offsets.

If I have an app that uses a char(80), versus an app that uses a
varchar(80), with an average width of data of 40, the char(80) data is
going to use approximately twice as many data pages to store the data.
That means twice as much disk I/O to read the table, which is where the
real bottleneck is.

I don't believe there is any tradeoff here.

Jul 23 '05 #4
Gary, see inline

Gary wrote:

You would never see a practical performance advantage in using char
over varchar, unless you had an extremely high transaction application.
The varchar offset lookup is optimized in-memory.
I agree that you won't see any performance degradation here.
The real bottleneck is disk I/O, not a few extra CPU cycles from looking up varchar
offsets.
Again, I agree
If I have an app that uses a char(80), versus an app that uses a
varchar(80), with an average width of data of 40, the char(80) data is
going to use approximately twice as many data pages to store the data.
That means twice as much disk I/O to read the table, which is where the
real bottleneck is.
This is only true in a perfect world scenario. If there is insufficient
free space to accomodate changes in the varchar data, then change
changes in the varchar(80) data will lead to fragmentation. Changes in
the char(80) data will not lead to fragementation, because any
replacement can be done in-place (assuming columns not part of a
clustered index).

So depending on the fill-factor, number of data changes, etc.
fragmentation will be a little or much greater for varchar compared to
char. This fragmentation is (as you probably know) especially expensive,
because it needs random I/O which is slower than sequential I/O.

I you reindex regularly, and have a sufficient fill factor, then
varchar(80) should always perform better if the average length is only
40.
I don't believe there is any tradeoff here.


Personally, I don't see a good reason why one would ever choose a
varchar over char when the maximum size is 4 characters or less. For
sizes over 10 characters I tend to choose varchar almost automatically.
For anything between 4 and 10 I really think about the situation before
deciding char or varchar.

Gert-Jan
Jul 23 '05 #5
Gert-Jan -

I totally agree with you. I simplified the situation quite a bit, but
with all other things being equal, yours is a good "guesstimat e".

I ran a test "perfect world" scenario of char(80) vs. varchar(80) (40
char avg len), and both the CPU time and disk I/O were about 40% higher
with the char(80) scenario with 10000 records.

Gary

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
10422
by: twkelsey | last post by:
Hi, My company has a scenario where we would like to change the data type of an existing primary key from an integer to a char, but we are concerned about the performance implications of doing so. The script for the two tables that we need to modify is listed below. Table FR_Sessions contains a column named TransmissionID which is...
10
71033
by: Techie | last post by:
what's the difference of the two data types?
22
11563
by: jdokos | last post by:
Hello, I have a question about VARCHAR fields. Our application groups here are starting to use VARCHARs much more frequently. Even VARCHAR (2) to (9) length fields. They say this is because some of the application programs, specifically Java Beans cannot handle the spaces after the value in CHAR fields. Is anyone else seeing this...
13
8458
by: Vivek | last post by:
Hi, Is it possible to insert a '\0' value or for that matter any control character into a DB2 CHAR/VARCHAR field ? So if i do a 'select hex(column) from table' i should see a 0 in the output. How can i do it from the command prompt ? Thanks, Vivek
1
6610
by: Troels Arvin | last post by:
Hello, In the DBMS I know best, PostgreSQL, there is no real performance difference between CHAR and VARCHAR. And since CHAR pads with spaces, I generally dislike CHAR (with CHAR, it's not possible to see if the input value had trailing spaces or not, for example). How is it in DB2? More specifically: If a column has type CHAR(20) and...
7
20435
by: D. | last post by:
Hi, I'm planning the structure of a SqlServer 2005 database for a new application. The requirement is that primary keys must be "natural"; i.e. in the table Customers the primary key will be a max. 10 characters string (but the string may be filled i.e. with only 5 charachters). Should I define these primary keys as char or varchar? I'm...
0
1347
by: Medhatithi | last post by:
I have a table whose indexed column is a char(16) field. I am giving the schema. CREATE TABLE Emp(Empno char(16), Ename varchar(25), Salary number(10,2),......) Now, there is an index on the Empno field. Now, inside a stored procedure, I am executing a statement select...
4
8855
by: Nick Chan | last post by:
all these while i've only used varchar for any string i heard from my ex-boss that char helps speed up searches. is that true? so there are these: 1) char with index 2) char without index 3) char with clustered index
0
27230
by: maheshmohta | last post by:
Background Often while remodeling legacy application, one of the important tasks for the architects is to have an optimum usage of storage capabilities of database. Most of the legacy applications are constrained by the technology available at the time of their development and hence aren’t optimum as per current scenario. One of such cases is the...
0
7546
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7740
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7985
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7503
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6071
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5387
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5111
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1962
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
784
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.