SQL Server - Char and Varchar Data Type
- 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 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Tags :
- Difference between Sql Server Char and Varchar Data Type