Home Icon Home Computer Science Difference Between CHAR And VARCHAR Data Types In SQL Server

Difference Between CHAR And VARCHAR Data Types In SQL Server

Char and Varchar are two data types used widely in SQL servers and for other programming purposes. The main difference is that Char stores fixed-length character strings, and Varchar stores character strings of variable lengths.
Unstop
Schedule Icon 0 min read
Difference Between CHAR And VARCHAR Data Types In SQL Server
Schedule Icon 0 min read

Table of content: 

  • Char vs Varchar
  • Differences between Char & Varchar
  • What are some more SQL datatypes?
  • Conclusion
expand icon

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

Char Varchar
It is an abbreviation for characters. It is an abbreviation for variable characters.
Char datatype is used to store character strings of fixed length. Varchar datatype is used to store character strings of variable length.
It uses static memory location. It uses dynamic memory location.
Char takes 1 byte space for each character. Varchar take 1 byte for each character along with some extra bytes to store length information.
We can use char datatype when we know the length of the string. We can use it when we are not sure of the length of the string.
Char datatype can be used when we expect the data values in a column to be of same length. Varchar datatype can be used when we expect the data values in a column to be of variable length.

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!
Edited by
Unstop

Tags:
Engineering

Comments

Add comment
No comments Image No comments added Add comment
Powered By Unstop Logo
Best Viewed in Chrome, Opera, Mozilla, EDGE & Safari. Copyright © 2024 FLIVE Consulting Pvt Ltd - All rights reserved.