Difference Between CHAR And VARCHAR Data Types In SQL Server

4 mins read

The two datatypes, char and varchar are used in SQL (structured query language) to store character strings of fixed and variable lengths respectively. SQL is a standard language that is used for accessing and manipulating databases. Let us see what are the differences between these two datatypes.

Char vs Varchar

The basic difference between Char and Varchar is that:

char stores only fixed-length character string data types whereas varchar stores variable-length string where an upper limit of length is specified.

1. Char(n) datatype

Char is a data type in SQL that can store characters of a fixed length. What is a datatype? A data type defines the type of data that a declared variable can hold. Data could be a string of single-byte or multibyte numbers, letters, numbers or any other characters that are supported by the database locale. It uses static memory location.

Ø Storage size – N bytes (set length)

Example:

Code –

CREATE TABLE Employee(Name VARCHAR(20), Designation CHAR(20));

SELECT LENGTH(Designation) FROM Employee;

Output –

20

2. Varchar(n) datatype

Varchar is a datatype in SQL that holds characters of variable length. This data type stores character strings of up to 255 bytes in a variable-length field. The data can consist of letters, numbers, and symbols. It uses dynamic memory location.

Ø Storage size – n bytes + 2 bytes

Example:

Code –

CREATE TABLE Employee(Name VARCHAR(50), Designation CHAR(20));

INSERT into Employee VALUES('Phoebe', ‘Software Developer');

SELECT LENGTH(Name) FROM Employee;

Output –

6

Differences between Char & Varchar

What are some more SQL datatypes?

When we declare a variable, we have a purpose to store a specific type of value in it. For example, a numeric value, alphabetical value, decimal value, etc. Each column and variable have a related data type in SQL server. We can divide all datatypes into 7 major categories such as,

1. Exact numeric data type: bit, tinyint, int, bigint, decimal, numeric, money, smallmoney and smallint.
2. Approximate numeric data type: Read and float.
3. Date and time data type: date, DateTime, datetime2, datetimeoffset, smalldatetime, time.
4. Character strings data type: char, varchar, text.
5. Unicode character strings data type: Nchar, Nvarchar, Ntext.
6. Binary strings data type: Binary, image and varbinary (stores binary byte strings rather than non-binary character strings)
7. Other data types: Cursor, hierarchy id, SQL variant, table, row version, unique identifier, XML, etc.

Conclusion

Even though varchar seems to be a better option, it is not wise to use it unless it is absolutely necessary. Char datatype allocates only as much memory as required, whereas varchar allocates extra spaces that many times go waste and occupy space unnecessarily. Hence, make sure to use the memory spaces wisely for better and optimized performance.

You may also be interested in reading:

1. Black Box Testing vs White Box Testing: Know the Key Differences
2. 21 points that can make your resume any HR's favourite
3. Enroll for free online programming courses in these top universities and get certified!