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

Read Other SQL Guru Questions