Difference between SQL Server Char a
nd Varchar Data Type
By Basavaraj P Biradar, 5 Jun 2013
3.88 (4 vot Rate this:
es)
inShare0
vote 1vote 2vote 3vote 4vote 5
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 10characters in this column.
On the other hand, VARCHAR is a variable length Data Type. For example, if you
declare a variable/column ofVARCHAR (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:
Collapse | Copy Code
DECLARE @CharName Char(20) = 'Basavaraj',
@VarCharName VarChar(20) = 'Basavaraj'
SELECT DATALENGTH(@CharName) CharSpaceUsed,
DATALENGTH(@VarCharName) VarCharSpaceUsed
Result:
Collapse | Copy Code
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:
Collapse | Copy Code
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:
Collapse | Copy Code
Surprise to me ...
Name
Length
----------------------------------------- ----------Basavaraj
Biradar
28
(1 row(s) affected)
Concatenation of VARCHAR variables:
Collapse | Copy Code
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:
Collapse | Copy Code
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
