What is the Difference between CHAR and VARCHAR in SQL Server - SQL Server / T-SQL Tutorial Part 31

CHAR and VARCHAR both data types are used to store character strings.

Char is fixed width, That means that the storage size of char value is equal to the maximum size of the column/ Variable.

Varchar is variable length. The Storage size used by Varchar depends upon the actual characters saved.

Example 1:

Let's create couple of variables and then save the same string and check the storage used by datalength function in SQL Server.

Declare @Variable1 CHAR(20)
Declare @Variable2 VARCHAR(20)

SET @Variable1='TechBrothersIT'
SET @Variable2='TechBrothersIT'

Select datalength(@Variable1) as VarLength1, datalength(@Variable2) as VarLength2

What is the difference between CHAR and VARCHAR in SQL Server - T-SQL Tutorial

As you can see above, Char occupied 20 Bytes space for 20 characters even we saved only 14 characters. On other hand, Varchar used only 14 bytes to store 14 characters.

Examples 2:

Let's create a table with two columns, once char and other varchat data type. Store the same information and then check the space occupied by each value by using datalength function.

Create table dbo.CharVsVarChar( CHARName CHAR(50),VARName VARCHAR(50))
insert into dbo.CharVsVarChar  
Select DataLength(CharName) AS CHARNameLength,DataLength(VarName) AS VarNameLength 
From dbo.CharVsVarChar

Char vs Varchar in SQL Server - SQL Server / T-SQL Tutorial 

We can see that Char always occupy the same space regardless number or characters saved. On other hand Varchar space occupied varies depending upon the number of characters saved.

In cases where we know that we are going to store fixed number of characters, we can use Char otherwise use Varchar.

No comments:

Post a Comment