Home » Beginner »sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

Demo: What’s the difference between CHAR and VARCHAR?

April 30, 2010 Beginner, sql server, sqlserverpedia-syndication, SSC, TSQL 2 Comments

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_Data
select‘[BEGIN]‘+ @myVarchar +‘[END]‘as Varchar_Data 

Here is the result when you run the script:

[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.

Happy days,
Jen McCown


Currently there are "2 comments" on this Article:

  1. Meher says:

    Very good post. so clear. Thank you.

  2. [...] Demo: What’s the difference between CHAR and VARCHAR? and String Data Types in SQL Server: VARCHAR vs. CHAR [...]

Comment on this Article:

Minion Reindex by MidnightDBA is here!


Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


Where are We?

November 3-7: PASS Summit, Seattle, WA

Novemmber 2: #SQLLongRun

November 3, 4-6pm: meetup with the MidnightDBAs at Top Pot Doughnuts on 5th Ave.

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/