SQL Server – Char vs. Varchar
Beginner developers often get confused about which types should they use when they create tables or what difference it makes.
Well, there is a good reason for the existence of all types – or at least there was when they were invented. I will soon publish a new series of posts about SQL data types, however, in this post I will talk about the most commonly misunderstood types and what troubles they likely to cause.
I found string types to be the most troublesome, especially Char. First and foremost Char is a fixed length type, which means that the space for the length of the characters is reserved. Opposed to this VarChar (acronym for variable character) is a variable length type which could save a considerable amount of space in large databases in exchange for some speed*.
When these types are created a length should be specified which can be up to 8000 in case of both Char and VarChar. However, with VarChar there is another option which is called MAX that is capable of holding data up to 2^31-1 bytes. There are default lengths for both depending on various circumstances but a responsible programmer, DBA would never leave anything to chances – so specify for an optimum length in either case!
Now about troubles… If you ever had to replicate tables you might have met a strange problem when suddenly trailing spaces filled up your string column where Char was used. There is a very simple reasons for this happening. Probably the most common is that SQL Management Studio sets ANSI_PADDING ON which leaves trailing spaces untrimmed therefore ANSI_PADDING should be turned OFF while CRATE TABLE or ALTER TABLE is executed.
Most of the time when you have already fallen into this trap a quick examination reveals that there was no reason to use Char in the first place and an ALTER to VarChar quickly makes the useless spaces disappear…
When it comes to designing however, following a couple of simple rules optimal results can be achievable:
- Use Char only if the rows of the column will be of the same or similar length (e.g. telephone numbers)
- Use VarChar when the variation of length is high, like names or email addresses
VarChar is introducing a considerable overhead for controlling the variable length so VarChar(1) is not a sensible choice for instance. Using your common sense is the quickest way to avoid a headache on the long run!
