CHAR and VARCHAR Data Types

This section details the usage and characteristics of the CHAR and VARCHAR data types in SQL Server.

CHAR Data Type

The CHAR(n) data type stores fixed-length character strings. When you declare a column as CHAR(n), each value stored in that column will always occupy exactly n bytes. If the string you store is shorter than n, it will be padded with spaces on the right to reach the specified length.

• n specifies the length of the string in bytes. It can be a value from 1 to 8000.
• Data is stored using single-byte character sets (e.g., ASCII). For Unicode characters, use NCHAR.
• Fixed length means storage space is allocated for the full length regardless of the actual string length, which can be less efficient for variable-length data.

Example Usage:

CREATE TABLE ProductCodes (
    Code CHAR(5) PRIMARY KEY,
    Description VARCHAR(100)
);

INSERT INTO ProductCodes (Code, Description) VALUES ('ABC', 'Standard Widget');
-- 'ABC' will be stored as 'ABC  ' (padded with spaces)
INSERT INTO ProductCodes (Code, Description) VALUES ('XYZ12', 'Premium Gadget');
-- This will fail if 'XYZ12' is longer than 5 characters.

VARCHAR Data Type

The VARCHAR(n) data type stores variable-length character strings. The amount of storage space used is determined by the actual length of the string you store, plus two bytes to store the length of the string. This makes VARCHAR more space-efficient for data where the length varies significantly.

• n specifies the maximum length of the string in bytes. It can be a value from 1 to 8000.
• If you specify VARCHAR(MAX), it can store up to 2 GB of non-Unicode character data.
• Data is stored using single-byte character sets. For Unicode, use NVARCHAR.
• Padding: Unlike CHAR, VARCHAR does not pad trailing spaces. However, if you insert a string shorter than n, it only stores the actual characters.

Example Usage:

CREATE TABLE CustomerNames (
    CustomerID INT PRIMARY KEY IDENTITY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

INSERT INTO CustomerNames (FirstName, LastName) VALUES ('Alice', 'Smith');
-- Stores 'Alice' and 'Smith' exactly as provided.
INSERT INTO CustomerNames (FirstName, LastName) VALUES ('Bob', 'Johnson');
-- Stores 'Bob' and 'Johnson' exactly as provided.

Key Differences Summary

Feature	CHAR(n)	VARCHAR(n)
Length	Fixed	Variable
Storage Space	n bytes + overhead	Actual string length + 2 bytes overhead
Padding	Pads with spaces to n bytes	Does not pad trailing spaces
Use Case	Fixed-format data (e.g., country codes, status flags)	General text data where length varies (e.g., names, descriptions)

Performance Considerations

• Storage: VARCHAR is generally more storage-efficient if string lengths vary. CHAR can lead to wasted space if strings are often shorter than n.
• Performance: For very small, fixed-length strings, CHAR might offer a slight performance advantage due to its predictable size. However, for most practical scenarios, the difference is negligible, and VARCHAR is preferred for its flexibility and storage efficiency.
• Comparisons: When comparing CHAR and VARCHAR values, SQL Server often treats them similarly. However, differences in padding can affect exact matches.

For Unicode character data, consider using NCHAR(n) and NVARCHAR(n) respectively, which use two bytes per character.