Click here to Skip to main content
15,121,549 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 23 Oct 2011

Tagged as

Stats

101.8K views
7 bookmarked

Difference between SQL Server Char and Varchar Data Type

Rate me:
Please Sign up or sign in to vote.
3.82/5 (6 votes)
5 Jun 2013CPOL1 min read
Difference between SQL Server Char and Varchar data type.

Everyone knows about the basic difference between CHAR and VARCHAR data types. In this article, apart from the basic difference, we will discuss on one more interesting difference which I have observed recently.

CHAR Data Type is a Fixed Length Data Type. For example, if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand, VARCHAR is a variable length Data Type. For example, if you declare a variable/column of VARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.
 
The below example illustrates the basic difference explained above:

SQL
DECLARE @CharName Char(20) = 'Basavaraj',
 @VarCharName VarChar(20) = 'Basavaraj'
SELECT DATALENGTH(@CharName) CharSpaceUsed,
 DATALENGTH(@VarCharName) VarCharSpaceUsed

Result:

SQL
CharSpaceUsed VarCharSpaceUsed
------------- ----------------
20            9
(1 row(s) affected)

Below is an interesting difference, which I have observed recently while writing some script.

Concatenation of CHAR variables:

SQL
DECLARE @FirstName Char(20) = 'Basavaraj',
  @LastName Char(20) = 'Biradar'
 
IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar'
 PRINT 'I was Expecting'
ELSE
 PRINT 'Surprise to me ...'
 
SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

Surprise to me ...
Name                                      Length
----------------------------------------- -----------
Basavaraj             Biradar              28
(1 row(s) affected)

Concatenation of VARCHAR variables:

SQL
DECLARE @FirstName VarChar(20) = 'Basavaraj',
 @LastName VarChar(20) = 'Biradar'
 
IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar'
 PRINT 'I was Expecting'
ELSE
 PRINT 'Surprise to me ...'
 
SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

I was Expecting
Name                                      Length
----------------------------------------- -----------
Basavaraj Biradar                         17
(1 row(s) affected)

So, it is clear from the above examples that during concatenation of CHAR data type variables, it includes space in-place of unused space in the result of concatenation.

Please correct me if my understanding is wrong. Comments are always welcome. Visit my blog: SqlHints.com for many more such articles on SQL Server. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Basavaraj P Biradar
Technical Lead http://SqlHints.com
India India
http://SqlHints.com/about/

Comments and Discussions

 
Questiondiff between datalength and len has the answer for your surprise Pin
Member 1159225214-Aug-16 18:27
MemberMember 1159225214-Aug-16 18:27 
GeneralMy vote of 3 Pin
Shaheed bin Muhammed21-Apr-14 20:46
professionalShaheed bin Muhammed21-Apr-14 20:46 
SuggestionYou can check this URL for differences Pin
jspanchal31-Aug-13 0:35
Memberjspanchal31-Aug-13 0:35 
BugActual length of varchar data Pin
joezen7775-Jun-13 11:42
Memberjoezen7775-Jun-13 11:42 
GeneralMy vote of 3 Pin
Klaus Luedenscheidt7-May-13 20:10
MemberKlaus Luedenscheidt7-May-13 20:10 
QuestionHi Pin
Ahsan Kabir15-Nov-12 22:04
MemberAhsan Kabir15-Nov-12 22:04 
GeneralReason for my vote of 5 Simple to understand! 5 from me. Pin
GPUToaster™24-Oct-11 23:52
MemberGPUToaster™24-Oct-11 23:52 
Reason for my vote of 5
Simple to understand! 5 from me.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.