November 27, 2008 at 6:14 am
Hi,
what are precise design considerations for choosing either CHAR or VARCHAR?
I know that char(10) uses exactly 10 bytes. I know that varchar uses less than 10 bytes if the string is say 5 characters long. But how much exactly?
I'd guess that the engine needs to store some delimiter (that one or two bytes) or some value that tracks the actual length of the string (say a smallint with 2 bytes).
And: Is there an overhead in memory management when using varchar that influences performance significantly? If I use a char and change the string no memory needs to be allocated whereas with varchar it should be necessary...
Thank you in advance
Alexander
November 27, 2008 at 6:27 am
It's a 2 byte overhead for storing the length (although that's not exactly what is stored on the pages)
There's no memory impact for expanding varchars, but it can cause page splitting if the varchar column grows and the page is full.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 6:33 am
Thank you for your reply.
So, what are your decision rules? (Like the maximum string length being greater than x and the variance of the strings' length being greater than y)
November 27, 2008 at 6:43 am
If all the strings in the table are going to be more or less the same length (within say 10 characters, depending on size) then char, otherwise varchar.
Things like ID numbers which are 13 characters long (south african ones anyway) or NULL are perfect for char. Postal codes, telephone numbers, much the same. Names vary too much and I'll make then varchar.
There aren't precise rules for when which should be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 7:10 am
My personal preference is to use varchar whenever I am not guaranteed a certain length. The examples Gail has given are good example if you are working on a database for a single country. My current project is international in scope so things like phone, postal code, and national id vary so I used varchar, but in a US only system I would have gone with char except for zip code since it could have the +4.
I know I have a read other places that you should use char when the max length is 10 or less.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply