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:

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.

Happy days,
Jen McCown
http://www.MidnightDBA.com

 

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:







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!

Where are We?

May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

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

The best database career advice you’ve never heard!

DBARoadmap.com

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/