Char/varchar datatypes

[Sashanan]

The SQL Server documentation informs me that:

- When a field has the char datatype, every value saved takes up as many bytes as the maximum length defined;
- When a field has the varchar datatype, every value saved takes up as many bytes as the value uses, and no more.

It then goes on to tell me that:

"Use char when the data entries in a column are expected to be consistently close to the same size.

Use varchar when the data entries in a column are expected to vary considerably in size."

It doesn't mention why, however. What reason(s) do I have to use char over varchar? Do I gain performance by doing so?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[JohnDTampaBay]

The difference is simply in storage space. The length of the data is also stored with a varchar type. There is a minute performance hit with varchar as SQL has to retreive the length of the data in the column. Since char is always the same length, no look up is necessary.

If the data length will almost always be close to the column length, then very little space is gained by using a varchar type. Conversely, if the data length varies greatly from the column length, substantial space can be saved by using varchar type.

I hope this helps. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Sashanan]

Thanks for the insight. The thing is, when I first designed the database I work with currently (say two dozen tables, number of columns per table varying anywhere between 5 and 50), I basically just made every field that'd contain text a varchar with the default max length of 50. Sometimes increasing it as the customer required it, sometimes decreasing it if I was positive it'd be less, like on codes following a specific format.

Result: I now have several hundred varchar(50) fields spread out over a bunch of tables, and many of them *could* be turned into char since the length is usually about the same. My question is, then, is it worth my trouble to do so? Will I see faster query execution or am I merely gaining milliseconds?


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[JamesLean]

Personally, I wouldn't bother. It would be a lot of work and you would need to test thoroughly to check you don't break any existing queries. The performance gain would be negligible if any.

--James

 

[Sashanan]

Figured as much but good to have it confirmed. Thanks to both of you!


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)