Varchar vs Varchar2 – Oracle PL/SQL Tutorial

When working with Oracle PL/SQL, developers frequently encounter the data types VARCHAR and VARCHAR2. At first glance, these might appear interchangeable, but they have distinct differences, both in their usage and historical context. Understanding these differences is critical for database developers and architects aiming to design robust and future-proof applications.

What Are VARCHAR and VARCHAR2?

In Oracle, VARCHAR and VARCHAR2 are used to store variable-length character strings. Unlike fixed-length data types such as CHAR, these types only use as much storage space as needed to hold the actual data, up to their defined maximum length.

For example, if you define a column as VARCHAR2(50) and store a string of 10 characters in it, only 10 characters’ worth of storage will be consumed, plus a small amount of overhead for managing the length. This makes these data types more storage-efficient than their fixed-length counterparts.

Key Differences Between VARCHAR and VARCHAR2

1. Historical Context

VARCHAR: This data type is primarily included in Oracle for ANSI compatibility. It was available in early versions of Oracle but is now considered deprecated. Oracle recommends against using VARCHAR in modern database design.

VARCHAR2: This is Oracle’s primary and preferred data type for variable-length strings. It is optimized for Oracle’s database architecture and supports the full range of Oracle features.

2. Semantics

VARCHAR: Historically, it allowed variable-length strings but was ambiguously defined in earlier Oracle versions. In some databases, it behaved similarly to VARCHAR2, while in others, it behaved like CHAR. This ambiguity led to its deprecation.

VARCHAR2: This type explicitly stores variable-length strings. It is well-defined and supports Oracle-specific features, making it the recommended choice.

3. Future Compatibility

Oracle has stated that the VARCHAR data type may be redefined in future releases to comply more strictly with the ANSI SQL standard. This could introduce changes in behavior for existing applications relying on VARCHAR.

On the other hand, VARCHAR2 is fully supported, and Oracle ensures backward compatibility for this data type.

4. Storage and Behavior

Both VARCHAR and VARCHAR2 store only the actual length of the string, plus overhead. However, the storage semantics and support for features like Unicode, multibyte characters, and null handling are more robust and predictable with VARCHAR2.

Practical Implications of Choosing VARCHAR2 Over VARCHAR

Given Oracle’s deprecation of VARCHAR, developers should exclusively use VARCHAR2 for new applications. For legacy systems that still use VARCHAR, a migration strategy should be considered to avoid potential compatibility issues in the future.

Here’s why VARCHAR2 is the better choice:

1. Performance and Optimization: Oracle has optimized VARCHAR2 for its database architecture, ensuring better performance and integration with Oracle-specific features.

2. Predictable Behavior: Unlike VARCHAR, which has ambiguous behavior in certain scenarios, VARCHAR2 offers consistent handling of strings, null values, and trimming.

3. Future-Proofing: Since Oracle has declared VARCHAR2 as its preferred data type for variable-length strings, using it ensures that your applications remain compatible with future Oracle versions.

Examples

Defining Columns

Suppose you’re creating a table to store user names. Here’s how you would define the column:

CREATE TABLE users (
    username VARCHAR2(100)
);

Using VARCHAR here instead of VARCHAR2 might work today but could lead to issues in the future as Oracle updates its database standards.

Querying and Storage Efficiency

When you insert a string into a VARCHAR2 column, only the actual characters are stored:

INSERT INTO users (username) VALUES ('JohnDoe');

In this case, only 7 bytes (for “JohnDoe”) are used, plus some minimal overhead, regardless of the 100-character limit.

Migration from VARCHAR to VARCHAR2

For older systems still using VARCHAR, you can migrate to VARCHAR2 with a simple ALTER TABLE statement:

ALTER TABLE users MODIFY username VARCHAR2(100);

This ensures that the column aligns with modern best practices.

Best Practices for Using VARCHAR2

Always Use VARCHAR2 for Variable-Length Strings: As the preferred data type, VARCHAR2 ensures compatibility, performance, and predictability in your Oracle applications.

Define Appropriate Lengths: While VARCHAR2 can store up to 4000 bytes (32,767 bytes in PL/SQL), defining excessively large limits can lead to inefficient memory usage and potential application-level bugs.

Consider Multibyte Characters: If your application supports multibyte character sets (e.g., UTF-8), ensure that the length of VARCHAR2 columns accounts for this. Oracle allows defining lengths in bytes or characters, depending on your needs.

CREATE TABLE multilingual_data (
    text VARCHAR2(100 CHAR) -- 100 characters, regardless of byte size
);

Avoid Using VARCHAR: Even for ANSI SQL compliance, refrain from using VARCHAR. If you need ANSI compatibility, consult Oracle’s documentation for alternative approaches.

Conclusion