The SQL Guru Answers your Questions...
Today's question comes from Bo G.
What's the latest thoughts on whether to use varchar or char? My thinking
was turned around lately when I heard that varchar fields do get padded out.
Previously I thought the benefit of varchars was that only the actual length
of the data consumed memory, not the field size. Now I've heard that a
varchar(255) field in SQL Server does allocate and use that much space for
each field's data. What's the scoop?
|
Hi Bo,
Well according to Microsoft, the difference between char and varchar is that
for varchar "Storage size is the actual length of the data entered, not n
bytes". That's from their Books On Line and confirmed on MSDN. They wrote
the package, so I'm pretty sure they know what they are talking about.
Just because you got me all curious, I created a test table with one varchar
column of length 8000 bytes and inserted 10 rows of 1 character into it.
With the data, the table size was still under 1 page (8k). If you theory
have been correct, the table would have been 9 or 10 pages in size.
So I think it's safe to assume that the Microsoft documentation is correct
and varchar fields only consume as much space as the data inside them
Regards
Owen
