Difference Between CHAR And VARCHAR In SQL Server

In SQL Server, CHAR and VARCHAR are used to store character strings, with CHAR being a fixed-length data type and VARCHAR a variable-length one. CHAR ensures consistent storage size for all entries while VARCHAR adjusting to varying string lengths, optimizing storage efficiency.

In this article, We will learn about the Difference Between CHAR And VARCHAR In SQL Server in detail.

What is CHAR?

• CHAR is a fixed character data type of SQL Server used to store character strings.
  
• Often when a string is less than the given length it adds spaces to the string to make the data fill the required length.
  
• This makes it suited for holding data of equal size only hence not suitable for data holding of human entries.

Features of CHAR

• Fixed Length: It helps in making a guarantee that each entry will take the same space which is a major boost in storage predictability.
• Padding: In a mode of its own; automatically fills with spaces until the required string length has been reached.
• Performance: Can be faster for operations that manipulate fixed-size data because storage and alignment are easier to predict.
• Storage: Does not have an incremental use of disk space, this is preferred especially when dealing with fixed-size fields.
• Use Case: Suitable for storing, data values, of which length is known in advance, number codes, identifiers of equal length, status codes, etc.

Example 1: Using CHAR

Let's Storing fixed-length codes like country codes.

SQL Query:

CREATE TABLE CountryCodes (
    Country CHAR(3)
);

INSERT INTO CountryCodes (Country) VALUES ('USA');
INSERT INTO CountryCodes (Country) VALUES ('IN');

SELECT Country, LEN(Country) AS Length FROM CountryCodes;

Explanation:

• In this example, the Country column is defined as CHAR(3), meaning it will store exactly 3 characters. If the input is shorter, it pads the value with spaces.
• The LEN() function is used to show the actual length of the stored string.

Output:

Even though 'IN' has only 2 characters, it occupies 3 characters in storage due to padding.

Example 2: Using CHAR

Let's Storing fixed-length employee IDs.

SQL Query:

CREATE TABLE Employees (
    EmployeeID CHAR(5)
);

INSERT INTO Employees (EmployeeID) VALUES ('E123');
INSERT INTO Employees (EmployeeID) VALUES ('A7890');

SELECT EmployeeID, LEN(EmployeeID) AS Length FROM Employees;

Explanation:

• Here, EmployeeID is defined as CHAR(5). The input 'E123' is padded with a space to make it 5 characters long.
• The LEN() function confirms the fixed length.

Output:

'E123' is stored as 'E123 ' with a space to ensure it meets the 5-character length.

What is VARCHAR?

• VARCHAR or variable character is a variable length data type found in SQL Server to store character strings.
  
• They only require storage space for the actual string together with a few bytes for storing the length of the string and are thus efficient for use with string data that is of varying length.

Features of VARCHAR

• Variable Length: Holds strings of different sizes as it only uses just enough space where the string will be stored in the data structure.
• No Padding: It does not add spaces at the end of shorter strings that reduce the storage size of the pre-written sentence strings.
• Storage Efficiency: Most of the records occupy little space as the records only use the needed space plus some extra space for string length.
• Flexibility: Ideal for fields where the length of data is typically large, for instance, names, descriptions or comments fields.
• Use Case: Most suitable when the length of the text data may vary from one record to another to optimize the use of storage space.

Example 1: Using VARCHAR

Let's Storing variable-length product descriptions.

SQL Query:

CREATE TABLE Products (
    ProductDescription VARCHAR(50)
);

INSERT INTO Products (ProductDescription) VALUES ('High-quality product');
INSERT INTO Products (ProductDescription) VALUES ('Compact and durable gadget');

SELECT ProductDescription, LEN(ProductDescription) AS Length FROM Products;

Explanation:

• In this example, ProductDescription is defined as VARCHAR(50), allowing it to store up to 50 characters. It uses only the necessary space for each entry.
• The LEN() function shows the actual length of the stored strings.

Output:

Each description is stored in its exact length without additional padding.

Example 2: Using VARCHAR

Scenario: Storing email addresses with varying lengths.

SQL Query:

CREATE TABLE Users (
    Email VARCHAR(100)
);

INSERT INTO Users (Email) VALUES ('[email protected]');
INSERT INTO Users (Email) VALUES ('[email protected]');

SELECT Email, LEN(Email) AS Length FROM Users;

Explanation:

• Here, the Email column is defined as VARCHAR(100), allowing each email address to be stored at its actual length, up to 100 characters.
• The LEN() function reveals the varying lengths of the stored email addresses.

Output:

The email addresses are stored exactly as entered, using only the required space.

Difference Between CHAR And VARCHAR In SQL Server

Conclusion

Overall, when it comes to CHAR and VARCHAR data type in SQL server it depends on the field’s nature and the specifics of the application. CHAR works well with fixed-length data and it has consistent storage and performance. While VARCHAR is flexible and suitable for storage since it does not take a fixed space for a record as CHAR does. Comparing the kinds of storage, performance and application means that proper decisions are made in using all the types of databases for efficient use.