Content level: Beginner / refresher
At last week’s SQL Saturday 41 Atlanta, I gave my TSQL Beginner’s Kit session in the last time slot of the day, to deafening applause and accolades. Actually, there were about 12 of us in the room, and we had a really good time. One of the more popular items in the session was a simple demonstration, illustrating the key difference between the SQL Server datatypes CHAR and VARCHAR. To review:
CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks. CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds.
VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information. For example, if you set a VARCHAR(100) datatype = ‘Jen’, then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.
You can see how the use of VARCHAR in most cases is preferred, to save space. The demo I used to illustrate this was a very simple script:
DECLARE @myChar CHAR(100),@myVarchar VARCHAR(100)SET @myChar =‘Jen’SET @myVarchar =‘Jen’select‘[BEGIN]‘+ @myChar +‘[END]‘as Char_Dataselect‘[BEGIN]‘+ @myVarchar +‘[END]‘as Varchar_Data
Here is the result when you run the script:
Char_Data [BEGIN]Jen [END] Varchar_Data [BEGIN]Jen[END]
See? It’s just a nice visual of all the extra padding (and space wasted) CHAR uses. The next tim someone asks you, “Hey, what’s the big deal? So I have 400 tables with CHAR(3000), so what?” you can point them right here.