Char v''s Varchar
-
Hi All,
My understanding was that if I define a field as char(200) and store for example "8" in it, it will take up 200 bytes.
If I define it as Varchar(200) it will take up 2 bytes, 1 byte for the character and 1 byte for the system to keep the length of the varchar.
So the varchar will save me physical disk space.
Is this correct?
I have been told that a varchar still takes up the same amount of disk space as a char which in my mind makes using a varchar fairly unproductive.
Can you point me to any resouces on this or clear this confussion from my mind.
TIA
Jon
-
Jon
From Books Online:
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
The above does not mean that every byte of storage saved by using varchar instead of char will be realised, because there will still be "wasted" space if you cannot fit the whole of a next row in an 8KB data page.
John
-
Hi,
Thanks for your response.
Yes I had checked books online and also googled quite a bit. Everything I found supported my view but the person I was discussing with seems adament that they are correct at which point I couldnt be 100% sure that I was correct.
What I cant seem to expalin is that if they both took up the same amount of space what would be the point in having char and varchar.
Jon
-
There are 2 bytes of overhead for recording the varchar length, not 1, but otherwise you are correct. If you copied a table with varchar(20) fields to a table where they were defined as varchar(2000), it would not require extra disk space for storage.
There would be some other effects however. When SQL processes the varchar(2000) table and allocates memory buffers, it must assume that some row may have 2000 chars in every field and it will grab enough memory to handle it. When you run a query in Query Analyzer or Management Studio in text mode, the result column widths come from the column definition. These are pretty minor effects, but declaring all your varchar fields as varchar(8000) would be a bad idea.
Maybe the person you are arguing with learned everything about databases in the dBASE era and hasn't been paying attention since then.
-
Scott's point about SQL having to assume that there could be 2000 chars of data even if there might only be 20 typically is also evident from the warning message you receive if you create a table where the potential column length is greater than SQLs 8k row limit. EG
create table x(
col1 varchar(8000),
col2 varchar(8000)
)
SQL Server will let you create the table, but row inserts/updates will fail if you attempt to fill both columns with 8000 characters.
On the same vein,
create table x(
col1 char(8000),
col2 char(8000)
)
will fail as the storage space must be >8k.
In any case, the usual advice is to use char(.) fields when you are storing things like postcodes that are always a certain length. Use varchar fields for descriptions, names etc. I had heard somewhere that the break-even point was about the 10 character mark but I cannot remember where (it would've been somewhere on these forums I imagine) or exactly why - might've been a combination of performance and storage considerations.
The final thing to note is that with char fields, depending on your data access layer API, you may store '1234' and when retrieving that value from a char(10) field receive '1234......' (the dots are spaces)... Annoying
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply