In my earlier articles, I wrote about char vs nchar and varchar vs nvarchar. In this article, I’ll write about the similarities, differences, advantages, disadvantages and usage of char and varchar. While designing database, we often came across using varchar or char for a column. This compilation of char vs varchar will help you to understand these SQL Server string data types and helps you to decide on how to use them.
Similarities Between Char And Varchar
- Both char and nchar are string data types.
- They can store only non-Unicode string data.
- Both data types has an optional argument specified as “n”. i.e. char [(n)] and varchar [(n|max)]. “n” defines the length of the string. the value of n ranges from 1 to 8000.
- While declaring a variable without specifying the argument “n”, the default length is 1.
- If, n is not specified while using along with CAST function, then the default length is 30.
- Both of them need 1 byte to store a character.
- Index can be created on both the data types.
Example: Usage Of Char & Nchar
/* Using Char */ Declare @c as Char(30); Set @c = 'Have a good day!'; Select @c; /*Results:*/ ------------------------------ Have a good day! (1 row(s) affected) /* Using Varchar */ Declare @v as Varchar(50); Set @v = 'Have a good day!'; Select @v; /*Results:*/ -------------------------------------------------- Have a good day! (1 row(s) affected)
Char Vs Varchar: Differences
| # |
Char |
Varchar |
|---|---|---|
| 1 | Fixed length string data type. | Variable length string data type. |
| 2 | Maximum string length which can be stored is 8000 bytes. | The maximum data storage size is 2^31-1 bytes. It is 2GB. The maximum storage size can be achieved by using the optional argument max. varchar(max) |
| 3 | The storage size is equal to the string length specified for the argument ‘n’. For example the length specified for the argument ‘n’ is 50, but the string data assigned to the variable has 20 characters, the storage size will be 50 irrespective of the length of the string stored in them. | The storage size is equal to the actual length of the data entered + 2 bytes. For example the length specified for the argument ‘n’ is 50, but the string data assigned to the variable has 20 characters, the storage size will be 20 + 2 = 22 bytes. |
| 4 | If you store string data with length lesser than the size of the declared size, then the remaining space is filled with space. So, you may need to use RTRIM function to trim the extra empty space. | Varchar won’t fill the remaining space as it is variable length data type. |
Char Vs Varchar: Usage
- Use Char data type if the length of the string you are storing is fixed for all the rows in the column. For example, storing SHA-256 hash data. SHA-256 hash codes are always 64 digit hexadecimal value. So, you can design a column with char(64) to store the SHA-256 hash code.
- Use Varchar data type if the length of the string you are storing varies for each row in the column. For example, name or description of a product.
- Use Varchar(max) data type if the length of the string you are storing will more than 8000 characters and varies considerably.
Example: Difference in storage size
/* Declare Char, Varchar and Varchar(max) */ Declare @c as Char(30); Declare @v as Varchar(30); Declare @m as Varchar(max); /* Assign values to the variables */ Set @c = 'Have a good day!'; Set @v = 'Have a good day!'; Set @m = 'Have a good day!'; /* Check the storage size */ Select DATALENGTH(@c), DATALENGTH(@v), DATALENGTH(@m); /*Results:*/ ----------- ----------- -------------------- 30 16 16 (1 row(s) affected)
Reference
- About char and varchar data types in Microsoft docs.