VARCHAR Maximum Limit and BYTE vs CHAR Length Semantics

• Engine-dependent – Oracle VARCHAR2 caps at 4000 bytes (32767 with extended strings), SQL Server VARCHAR(n) up to 8000, PostgreSQL up to about 1 GB, MySQL up to 65535 bytes shared across the row.
• BYTE semantics – VARCHAR2(10 BYTE) allows 10 bytes of storage; a multi-byte character may consume 2-4 bytes.
• CHAR semantics – VARCHAR2(10 CHAR) allows 10 characters regardless of byte width.
• UTF-8 impact – under UTF-8, a Hindi or accented character can take 2-4 bytes, so a BYTE-sized column may reject a short name.
• Best practice – use CHAR semantics for human-readable text (names, addresses) and always confirm the engine and character set.

What is the maximum VARCHAR length per database engine?

The max is not a single universal number; it depends on the engine.

┌──────────────┬─────────────────────────────────────────────┐
│ Engine       │ Maximum VARCHAR Length                       │
├──────────────┼─────────────────────────────────────────────┤
│ Oracle       │ VARCHAR2: 4000 bytes (32767 with extended)  │
│ SQL Server   │ VARCHAR(n): 8000, VARCHAR(MAX): ~2 GB       │
│ PostgreSQL   │ VARCHAR(n): ~1 GB, TEXT: same               │
│ MySQL        │ VARCHAR: up to 65535 (shared row max)       │
└──────────────┴─────────────────────────────────────────────┘

The correct interview answer is to state it is engine-dependent and give the specific engine's number.

What are BYTE length semantics?

With BYTE semantics, the declared length counts bytes of storage.

-- Oracle: 10 bytes of storage
CREATE TABLE customers (
    name VARCHAR2(10 BYTE)
);

-- English name: 'Rajesh' = 6 bytes (1 byte per char in UTF-8) ✓
-- Hindi name: a 4-character Hindi name might need 12 bytes
-- ERROR: ORA-12899: value too large for column "NAME"

What are CHAR length semantics?

With CHAR semantics, the declared length counts characters regardless of byte width.

-- Oracle: 10 characters of storage
CREATE TABLE customers (
    name VARCHAR2(10 CHAR)
);

-- English name: 'Rajesh' = 6 characters ✓
-- Hindi name: 4 characters (even if 12 bytes) ✓
-- The database allocates enough bytes to store 10 characters

Why does the difference matter under UTF-8?

Under a single-byte encoding like Latin-1, 10 characters equal 10 bytes, so BYTE and CHAR are identical. Under UTF-8, a character can take 1-4 bytes.

┌──────────────┬─────────────┬──────────────────────────────┐
│ Character    │ UTF-8 Bytes │ Impact on BYTE-sized column   │
├──────────────┼─────────────┼──────────────────────────────┤
│ A-Z, 0-9     │ 1 byte      │ Same as CHAR semantics        │
│ Accented (e) │ 2 bytes     │ Halves effective capacity     │
│ Hindi (क)    │ 3 bytes     │ Thirds effective capacity     │
│ Emoji        │ 4 bytes     │ Quarters effective capacity   │
└──────────────┴─────────────┴──────────────────────────────┘

At National Bank of India, Priya's Hindi name was rejected by a BYTE-sized column even though it looked short.

How do different engines handle this?

| Engine | VARCHAR(n) counts | How to control |
|---|---|---|
| Oracle | BYTE or CHAR (explicit) | Per-column BYTE/CHAR or global NLS_LENGTH_SEMANTICS |
| PostgreSQL | Characters | Always character-based; no BYTE option |
| SQL Server | Bytes for VARCHAR, characters for NVARCHAR | Use NVARCHAR for Unicode |
| MySQL | Characters | VARCHAR(n) counts characters; 65535 row limit is in bytes |

-- PostgreSQL: VARCHAR(n) always counts characters
CREATE TABLE customers (
    name VARCHAR(50)  -- 50 characters, any encoding
);

-- SQL Server: NVARCHAR for Unicode
CREATE TABLE customers (
    name NVARCHAR(50)  -- 50 characters, Unicode-safe
);

How do you set CHAR semantics globally in Oracle?

-- Oracle: set default length semantics for the session
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

-- Or set it at the database level
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS = CHAR;

-- Verify
SELECT * FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_LENGTH_SEMANTICS';

What happens when truncation splits a multi-byte character?

If a column stores a value that is later truncated at a byte boundary, it can split a multi-byte character, producing corrupt data.

package com.bank.service;

public class CharTruncationDemo {
    public static void main(String[] args) {
        String hindi = "\u0915\u0916\u0917\u0918"; // 4 Hindi chars, 12 bytes
        byte[] bytes = hindi.getBytes(java.nio.charset.StandardCharsets.UTF_8);

        // Truncating at byte 10 splits the 4th character
        String truncated = new String(bytes, 0, 10,
            java.nio.charset.StandardCharsets.UTF_8);
        // Result: 3 full characters + a replacement character (corrupt)
    }
}

What is the best practice for banking applications?

For any human-readable text like names and addresses, use CHAR semantics so the length means what business users expect.

-- Oracle: explicit CHAR semantics
CREATE TABLE customers (
    name        VARCHAR2(100 CHAR) NOT NULL,
    address     VARCHAR2(500 CHAR),
    pan_number  CHAR(10)  -- fixed-length, always 10 ASCII characters
);

-- PostgreSQL: already character-based
CREATE TABLE customers (
    name    VARCHAR(100) NOT NULL,
    address VARCHAR(500)
);

-- Always set the database character set to UTF-8
-- PostgreSQL: CREATE DATABASE national_bank ENCODING 'UTF8';

What should a senior engineer know?

• Always confirm the engine, character set, and collation before sizing columns.
• Use CHAR semantics (or character-counting engines) for human text.
• Be explicit about collation so comparisons and sorting behave correctly.
• Reserve BYTE sizing for binary or known-ASCII fields like hash values.
• Use generous sizing for names: 100 characters is a safe minimum.
• Store money and identifiers in fixed, well-understood types, not VARCHAR.