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, 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 no. 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.

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.

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

19 thoughts on “Difference between Sql Server Char and Varchar Data Type

  1. Hi SQL hints,
    DB2 iSeries server, we can define a field firstname varchar(50) allocate(12)
    this means the row contains 12 characters for the field, any further characters go to overflow area. As long as 99% of rows stay within 12 characters, logical access is extremely fast.
    Question please for SQL Server
    does varchar(12) achieve same fast result (12 = to the allocated physical size in the row)
    Is their a tool to maintain the 12 in varchar(12)

  2. Can you clarify?
    I ran below statement
    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,
    datalength(@FirstName + ‘ ‘ + @LastName) AS [DataLength]

    Output is :
    Basavaraj Biradar 28 41

Leave a Reply

Your email address will not be published. Required fields are marked *