SQL Server - Char and Varchar Data Type

Wikitechy | 2656 Views | sql server | 08 Jun 2016

 


  • CHAR Data Type is a Fixed Length Data Type which will store 1 byte per character and once declared the variable will acquire the length defined.

Example1:

  • If we declare a variable/column -> CHAR (10) data type, then it will always take 10 bytes irrespective of whether we are storing 1 character or 10 character in this variable or column.
  • The max characters to store is 10 in the above variable / column.

                         Ex: declare @wikitechy char(10) as ‘venkat’

  • The above example will allocate 10 bytes and store 6 bytes of data leaving 4 bytes free
  • varchar Data Type is a variable Length Data Type which will store 1 byte per character and once declared and assigned, the variable will acquire the length based on the data assigned.

Example2 :

  • If we declare a variable/column ->VARCHAR (10) data type, then it won’t take any space and once assigned the space will be taken based on the data assigned and max 10 character can be stored in this variable or column.

                Ex: declare @wikitechy varchar(10) as ‘venkat’

Storage Perspective Between Char Vs Varchar :
  • In the below code, we are able to see wikitechy occupies 9 bytes and the rest is not allocated in varchar and it’s allocated in the char data type.

Sample Code :

DECLARE @CharName Char(20) = 'WikiTechy',
  @VarCharName VarChar(20) = 'WikiTechy'
SELECT DATALENGTH(@CharName) as CharSpaceUsed,-- complete space allocated DATALENGTH(@VarCharName) as VarCharSpaceUsed -- space allocated only for the data assigned


Applies to sqlserver version : 

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Tags :

  • Differences Sql Server
  • Tips/TricksChar
  • Char Vs Varchar
  • Data Type
  • Difference between Sql Server Char and Varchar Data Type
  • Sql Server, TIps/Tricks
  • VARCHAR
  • Varchar Vs Char




Workshop

Bug Bounty
Webinar

Join our Community

Advertise
<