Use CHAR instead of VARCHAR when the size of the content is known in advance
When browsing the MSDN forums, I see a lot of people using the datatype VARCHAR for fixed length columns. The VARCHAR datatype is good if you don't know the size of the content in advance but if you do know the size in advance it is better to use the CHAR datatype.
Here is a comparison between both datatypes concerning the storage:
| Data Type | Range | Storage |
|---|---|---|
| CHAR(n) | 1 to 8000 characters | (n) bytes |
| NCHAR(n) | 1 to 4000 unicode characters | (n * 2) bytes |
| VARCHAR(n) | 1 to 8000 characters | Variable. Storage is the actual length plus 2 bytes. |
| NVARCHAR(n) | 1 to 4000 unicode characters | Variable. Storage is the actual length plus 2 bytes. |
The difference is that the VARCHAR and the NVARCHAR data types, need an additional 2 bytes opposed to theire fixed versions. You wont see the difference with small tables. For bigger tables, this can give a waste of disk space and be less performant since the table requires more pages.
3 comments:
The number of bytes you save per row due to its dynamic size outweights the 2 byte overhead
That's true if the column is dynamic but that's why the title says "when the size of the content is known in advance".
people that use a varchar for a fixed sized field should be hang and shot, and then hang again :-)
Post a Comment