MSDN Documentation

1. Introduction
2. Syntax
3. Description
4. Characteristics
5. Usage Examples
6. Comparison with other types
7. Performance Considerations
8. See Also

1. Introduction to the VARCHAR Data Type

The VARCHAR data type in SQL is used to store variable-length non-Unicode character strings. It is one of the most commonly used data types for storing textual information, such as names, addresses, descriptions, and more. Unlike fixed-length character types like CHAR, VARCHAR only stores the characters that are actually present, plus a small overhead for storing the length of the string.

This documentation provides a comprehensive overview of the VARCHAR data type, covering its syntax, characteristics, typical usage scenarios, and performance considerations.

2. Syntax

The general syntax for declaring a VARCHAR column varies slightly between different SQL database systems (e.g., SQL Server, MySQL, PostgreSQL, Oracle), but the core concept remains the same.

Here are common syntaxes:

-- SQL Server Syntax
VARCHAR ( n | ( max ) )

-- MySQL Syntax
VARCHAR(length)

-- PostgreSQL Syntax
VARCHAR(n)
CHARACTER VARYING(n)

-- Oracle Syntax
VARCHAR2(size [BYTE | CHAR] )

Where:

n: Specifies the maximum number of characters that can be stored. The range for n typically goes from 1 to a system-defined maximum (e.g., 8000 in SQL Server, 65535 in MySQL, 10485760 bytes in PostgreSQL).
max (SQL Server): Indicates that the maximum storage size is 2^31-1 bytes.
length (MySQL): Similar to n, specifying the maximum character length.
BYTE | CHAR (Oracle): Specifies whether size is in bytes or characters.

3. Description

VARCHAR is designed for strings where the length can vary significantly from row to row. When you declare a column as VARCHAR(50), it means the column can hold up to 50 characters. However, if you insert a string of only 10 characters, the database will only allocate storage for those 10 characters plus some length information, rather than the full 50 characters.

This storage efficiency makes VARCHAR a highly flexible and popular choice for many types of text data.

4. Characteristics

Variable Length: Stores only the characters entered plus length information.
Character Set Dependent: The actual storage size can depend on the character set and collation of the database or column. For example, a multi-byte character set can mean that VARCHAR(50) can store fewer than 50 characters if those characters require more than one byte.
Maximum Length: Each database system has a defined maximum length for VARCHAR columns.
Performance: Generally good for typical operations, but very large strings or frequent manipulations can impact performance.
Nullability: Can store NULL values, indicating the absence of a value.

5. Usage Examples

Creating a table with VARCHAR columns


CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Description VARCHAR(500),
    SKU VARCHAR(20) UNIQUE
);

Inserting data into VARCHAR columns


INSERT INTO Products (ProductID, ProductName, Description, SKU)
VALUES (1, 'Laptop Pro', 'A high-performance laptop with a 15-inch display.', 'LP-15-X1');

INSERT INTO Products (ProductID, ProductName, SKU)
VALUES (2, 'Wireless Mouse', 'Ergonomic wireless mouse.'); -- Description is optional

Querying VARCHAR columns


SELECT ProductName, SKU
FROM Products
WHERE ProductName LIKE 'Laptop%';

6. Comparison with other string types

Feature	VARCHAR	CHAR	NVARCHAR / NCHAR
Length	Variable	Fixed	Variable (NVARCHAR) / Fixed (NCHAR)
Storage	Efficient (stores actual length + overhead)	Allocates full declared length, padded with spaces	Stores Unicode characters; uses more space per character
Use Case	Names, addresses, variable descriptions	Fixed-format codes, flags, short, consistent strings	Storing international characters, text requiring full Unicode support

7. Performance Considerations

Max Length: While you can declare a very large size, consider the practical maximum length of data you'll store. Unnecessarily large declarations can waste space and potentially impact performance.
Indexing: Indexing VARCHAR columns can be effective, but be mindful of index size. Indexing the full length of very long VARCHAR columns can create large indexes, slowing down index operations. Consider using prefix indexing if your database supports it.
Comparisons and Sorting: Comparisons and sorting of VARCHAR data are generally efficient. However, collations can affect performance if they involve complex rules or case-sensitivity.
Character Sets: Using multi-byte character sets can increase storage requirements. Understand your character set's byte per character ratio.
Null vs. Empty String: In many SQL dialects, a NULL value is distinct from an empty string (''). Be aware of how your system treats them.

Table of Contents