How much variation in a column's length can occur before a VARCHAR should be considered over CHAR for performance? I believe if a column can vary in length by no more then 5 characters a CHAR is appropriate, but I'm not certain if this is correct. What are your thoughts?

Thanks, Dave

It is probably a personal preference kind of thing, but I prefer to use varchar for anything that has a variable length, regardless of how long it might be (even a char(1) NULL column)

Cheers
Twan

I know for older versions of SQL Server (6.0 and 6.5) the rule was a variation of 4 or 5 characters (I don't recall the exact number). The overhead of SQL Server calculating the actual length of the data made it beneficial to follow this rule. However with SQL Server 2000 I'm not sure if the overhead is significant enough to consider such a rule.

Thanks, Dave

I tend to agree with Twan. If a field is going to contain a variety of lengths, then use varchar.
IF a column is always the same length, always, always, always, then regardless of how long, use char. Varchar carries a single bite overhead and if you will always fill all the characters every time, then you dont need the overhead. Examples of this are 2 digit state/province code, or 5 digit membership numbers etc.
There is no need for a varchar(1) - it makes no sense, either there is a value, length of 1 or not.

The other smaller values would depend mostly on your data and how its used. But if you have variable data, use a variable length, if you have fixed length then use fixed length.

Chris

I go for a different approach, I tend to choose char if the difference between min and max is less than about 10 characters. But its worth noting, you should study your row size carefully. Even if they will rarely be used it is occasionally better to pad a record to 20 characters or whatever simply to have the row size a nice division of the page size, minimising page splits.

You need to be careful when trying this approach though, but it can pay off

I render :
Char is stored as a fixed length string, Varchar is stored as a variable length string. Thus, if you define the table char & varchar with both lengths as 50 and if you perform insert(char,varfield) Values ('Hello','Hello').
The first fields will take 50 bytes while the second will take only 5. It is usually recommended to store strings as varchar.

---

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Satya,

What rule do you follow in determining whether or not to assign char or varchar to a field that only has a small variation in length? For example, if the minimum length of a field is 7 bytes and the maximum length is 9 bytes, is there a performance gain in choosing char(9) vs. varchar(9).

Thanks, Dave

In the database -- a CHAR is a VARCHAR that is blank padded to its maximum length.
I would go with varchar only as the empty space of varchar is not used.

---

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

remember also that a char(x) NULL column is actually a varchar(x) NULL column. A char column is incapable of holding a null, so behind the scenes SQL will actually use a varchar field.

Cheers
Twan

Satya,

What about the overhead associated with calculating the actual length of a varchar's data? Back in the early days of SQL Server that was considered a factor in choosing char or varchar. Do you know if this overhead is now considered too insignificant to worry about? It#%92s for this reason I#%92m trying to determine if there is a maximum size variation to consider, ie a difference of 4 or 5 characters, before going to varchar.

Twan,

Good point about NULL char being treated like varchar internally. That makes it an easy decision when a column can be NULL.

Dave

Dave

If you aren't using 6.5 version then better not to worry about this overhead using CHAR/VARCHAR.
BTW, how big would be the table size and what kind of activity expected from the application on this table especially?

---

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.