SQL VARYING

What does the SQL VARYING keyword do?

VARYING is used with string data type keywords (CHAR, CHARACTER, NCHAR) to declare a variable-length character column or variable holding up to a specified maximum length.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VARYING: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, IBM Db2, Snowflake, Redshift, BigQuery, Trino/Presto

SQL VARYING Full Explanation

VARYING is not a standalone data type; it modifies CHAR, CHARACTER, or NCHAR to create a variable-length string type whose storage grows and shrinks with the actual data length up to a defined limit. The most common shorthand is VARCHAR, an ISO-standard synonym for CHARACTER VARYING. Unlike fixed-length CHAR(n), which always pads values to n characters, CHARACTER VARYING(n) stores only the supplied characters plus minimal overhead, improving storage efficiency and often query performance. If a length is omitted in some dialects, the column is treated as unlimited or defaults to a vendor-specific maximum. Attempting to insert a value longer than n results in an error or truncation, depending on the SQL mode and database settings. VARYING applies only to character types; there is no numeric or temporal equivalent. Because VARCHAR is more widely recognized, CHARACTER VARYING appears mainly in standards documents and highly portable code.

SQL VARYING Syntax

-- column definitions
CREATE TABLE example (
    fixed_name CHAR(50),
    var_name CHARACTER VARYING(50),
    alt_name CHAR VARYING(50),
    unicode_name NCHAR VARYING(100)
);

-- variable declaration (PL/pgSQL shown)
DECLARE
    v_title CHARACTER VARYING(255);
END;

SQL VARYING Parameters

n (integer) - Maximum number of characters allowed (required in most dialects, optional in PostgreSQL and Redshift)

Example Queries Using SQL VARYING

-- 1. Create a table mixing fixed and varying character columns
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn CHAR(13),
    title CHARACTER VARYING(200),
    subtitle VARCHAR(200)
);

-- 2. Attempt to insert an over-length value
INSERT INTO books(isbn,title)
VALUES ('9780131103627', RPAD('A',201,'A'));

Expected Output Using SQL VARYING

Query 1 creates the table with title stored as variable-length up to 200 characters
Query 2 raises an error: value too long for type character varying(200)

Use Cases with SQL VARYING

Defining textual columns where most values are shorter than the maximum length
Reducing on-disk storage compared with fixed-length CHAR
Allowing flexible input lengths for user-generated content, file paths, JSON snippets, etc.
Declaring PL/SQL or PL/pgSQL variables expected to hold variable-length strings

Common Mistakes with SQL VARYING

Assuming VARYING alone is a valid type (it must follow CHAR, CHARACTER, or NCHAR)
Forgetting to specify a length and unexpectedly getting a vendor-specific maximum
Believing VARCHAR and CHARACTER VARYING behave differently (they are synonyms)
Mixing CHAR and VARCHAR in joins and comparing padded versus unpadded strings, which can impact performance
Expecting automatic truncation when strict SQL modes raise an error on overflow

First Introduced In

SQL-92 standard (as CHARACTER VARYING)

Frequently Asked Questions

Is VARCHAR identical to CHARACTER VARYING?

Yes. VARCHAR is a synonym defined by the SQL standard. Both declare variable-length character strings.

Do I need to provide a length for CHARACTER VARYING?

In most dialects yes, but PostgreSQL and Redshift allow omitting it, treating the field as unlimited or very large.

Does using VARYING harm performance compared with CHAR?

No for typical workloads. VARCHAR saves space and usually matches CHAR performance. CHAR may be marginally faster only when every stored value is exactly the fixed length.

How do databases handle over-length inserts?

By default they raise an error like "value too long for type character varying(n)". You can enable relaxed modes in some systems to auto-truncate, but this is discouraged.