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:
DECLARE @CharName Char(20) = 'Basavaraj',
@VarCharName VarChar(20) = 'Basavaraj'
SELECT DATALENGTH(@CharName) CharSpaceUsed,
DATALENGTH(@VarCharName) VarCharSpaceUsed
Result:
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:
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:
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.
