Today's question comes from Bo G.
What's the latest thoughts on whether to use varchar
orchar
? My thinking was turned around lately when I heard thatvarchar
fields do get padded out. Previously I thought the benefit ofvarchars
was that only the actual length of the data consumed memory, not the field size. Now I've heard that avarchar(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
Read Other SQL Guru Questions |