The Legacy Obituaries

Notices of desupport & departureOracle Database → AzureTuesday, 29 September 2026The Coroner’s ReportsAbout this paper

Case q14 · fact

Our VARCHAR2(100) columns hold multibyte text. Anything to watch on Azure SQL?

Expected: Oracle length semantics may be BYTE; SQL Server varchar(n) is bytes in a single-byte collation, nvarchar(n) is characters (UTF-16) — choose nvarchar or UTF-8 collation; SSMA maps varchar2 to varchar by default.

No tools

The model alone, answering from memory.
larger model · 1 turn · 24 s · 16.5k in / 2.4k out

✓ verdict ✗ grounded ✗ cited

Grader: Correct and thorough; many uncited numbers (index limits, JDBC)

Answer

Verdict: Yes. The length number means different things on each side, so a straight VARCHAR2(100) → varchar(100) mapping can truncate your data or fail the load. First check whether your columns count bytes or characters, then pick nvarchar or a UTF-8 collation and size the column to fit.

1. Check the length semantics in Oracle first

  • VARCHAR2(100) means 100 bytes or 100 characters, depending on NLS_LENGTH_SEMANTICS, or an explicit BYTE/CHAR on the column.
  • Check with SELECT column_name, char_used, char_length, data_length FROM dba_tab_columns .... CHAR_USED is B for bytes or C for characters.
  • With AL32UTF8, a 100 CHAR column can hold up to 400 bytes.
  • Profile the real data with MAX(LENGTHB(col)) and MAX(LENGTH(col)). Look for supplementary characters (emoji, rare CJK), which take 4 bytes in UTF-8.

2. In SQL Server, n is never "characters"

3. Your two target options

Option Mapping for VARCHAR2(100 CHAR) Notes
nvarchar (classic) nvarchar(100), or nvarchar(200) if emoji/supplementary characters exist ASCII text takes 2× storage. Row/page compression's Unicode compression helps.
varchar + UTF-8 collation (e.g. Latin1_General_100_CI_AS_SC_UTF8) varchar(400) for the worst case, or size from measured LENGTHB Byte semantics like Oracle BYTE. Compact for mostly-Latin text, bigger for CJK.

4. Knock-on issues

  • Index key limits: 1700 bytes for nonclustered keys and 900 for clustered. Widened columns in composite keys can go over.
  • Implicit conversions: mixing varchar columns with nvarchar parameters causes conversions and index scans. The classic case is JDBC sendStringParametersAsUnicode=true against varchar columns. Keep column and parameter types the same.
  • String literals: use the N'...' prefix for nvarchar literals, or the text gets mangled.
  • Collation behavior: Oracle compares with binary, case-sensitive rules by default. Azure SQL's default collation is case-insensitive (SQL_Latin1_General_CP1_CI_AS), so unique constraints that worked in Oracle can fail and query results can change. Choose the collation on purpose (_CS, _BIN2, or _SC for supplementary characters).
  • Other semantics: Oracle treats '' as NULL and SQL Server doesn't. SQL Server also ignores trailing spaces in = comparisons.
  • Length functions: LEN/DATALENGTH don't behave like LENGTH/LENGTHB. LEN counts supplementary characters as 2 unless you use an _SC collation. Check any code that validates lengths.

Recommendation: Profile the data first. Use nvarchar with an _SC collation, or a _UTF8 collation, and size from the measured maximums plus some headroom. After the load, compare row counts and per-column LENGTH/HASHBYTES checksums with Oracle.

Keyword search

BM25 over the same 446 documents: top 8 passages pasted in, one pass, references not followed.
larger model · 1 turn · 8 s · 18.9k in / 0.8k out

✓ verdict ✓ grounded ✗ cited

Grader: Correct verdict incl. BYTE default and SSMA; no URLs

8 keyword-search passages
  1. caveat_pg-varchar-counts-characters score 17.23
  2. mapping_varchar2-byte-semantics--azure-pg-flex score 15.711
  3. mapping_number-unconstrained--azure-pg-flex score 11.467
  4. feature_varchar2-byte-semantics score 10.474
  5. target_elastic-jobs score 10.324
  6. feature_boolean-23ai score 9.615
  7. caveat_pg-hint-plan-needs-preload score 9.59
  8. mapping_varchar2-byte-semantics--azure-sql-db score 8.51
Answer

Verdict: Yes. SSMA's default mapping of VARCHAR2(100) to varchar(100) can lose multibyte text or reject rows as too long, so change the target type and translate each length instead of copying it.

Key facts (from [8] and [4]):

  1. Data loss with the default mapping. SSMA maps varchar2(n) to varchar(n) by default. SQL Server varchar stores text in the code page of its collation. Multilingual data from an AL32UTF8 Oracle database is either lost or has to go into one of these:

    • nvarchar(n), or
    • varchar(n) under a UTF-8 (_UTF8) collation, which Azure SQL supports.

  2. The two engines count length differently.

    • Oracle BYTE semantics count bytes. BYTE is the default when neither the declaration nor NLS_LENGTH_SEMANTICS says otherwise.
    • Oracle CHAR semantics count characters.
    • SQL Server varchar(n) counts bytes.
    • SQL Server nvarchar(n) counts UTF-16 code units (byte pairs), so supplementary characters take two units each and can overflow.

    The right target length for a VARCHAR2(100 BYTE) column in an AL32UTF8 database depends on the data. It may need to be smaller or larger.

  3. Mixed semantics make blanket rules unsafe. If some columns in the schema use BYTE and others use CHAR, no single mapping rule works. Size each column on its own terms.

What to do:

  • On the source, check NLS_CHARACTERSET and NLS_LENGTH_SEMANTICS, and how each column was declared (BYTE or CHAR).
  • For non-ASCII data, change the SSMA mapping to nvarchar(n), or create the database with a _UTF8 collation.
  • If you use a UTF-8 collation, widen columns declared with CHAR semantics. A character can take up to 4 bytes, so VARCHAR2(100 CHAR) may need up to varchar(400).
  • Watch for "value too long" errors during the load. They are the usual sign that sizing went wrong.
  • Test sorting and comparisons. Oracle's binary sort order differs from SQL Server collations.

The passages don't include source URLs, so I can't give links.

Sanity Context · GROQ

The agent with the GROQ endpoint: schema, joins, Portable Text.
smaller model · 5 turns · 36 s · 50.4k in / 2.6k out

✓ verdict ✓ grounded ✗ cited

Grader: All points covered; MI collation claims cited; header scope inconsistent

4 tool calls
  1. initial_context on groq · 4,126 chars back
  2. groq_query on groq · 2,907 chars back
    *[_type=="oracleFeature" && (category=="datatype" || name match "*char*" || name match "*VARCHAR*" || name match "*multibyte*" || name match "*NLS*" || name match "*Unicode*")]{_id, name, "slug": slug.current, category, introducedIn, deprecatedIn, desupportedIn, oracleReplacement}
  3. groq_query on groq · 7,950 chars back
    *[_type=="oracleFeature" && slug.current in ["varchar2-byte-semantics","character-set"]]{
  name, "slug": slug.current, category, introducedIn, deprecatedIn, desupportedIn,
  "mappings": *[_type=="mapping" && oracleFeature._ref==^._id]{
    fidelity, effort, rationale, appliesToOracleVersions,
    "azureTarget": azureTarget->{name, service},
    "sources": sources[]->{title,url},
    "caveats": *[_type=="caveat" && mapping._ref==^._id]{
      severity, statement, appliesTo, firsthand,
      "evidence": evidence->{title,url,publisher}
    }
  }
}
  4. groq_query on groq · 2,454 chars back
    *[_type=="dispute" && (claimA._ref in ["caveat_sqlserver-utf8-collation-for-varchar","caveat_varchar2-ssma-varchar-default"] || claimB._ref in ["caveat_sqlserver-utf8-collation-for-varchar","caveat_varchar2-ssma-varchar-default"])]{title, whatDisagrees, resolution, "claimA": claimA->statement, "claimB": claimB->statement, "resolvedBy": resolvedBy->{title,url}}
Answer

Verdict: Multibyte data in Oracle VARCHAR2(100) needs deliberate collation/type planning before migrating — the default Azure SQL migration path (SSMA) can silently mangle it, and lengths may need adjusting.

Key facts (Oracle 11.2–26ai → Azure SQL Database / Managed Instance, fidelity: partial):

  • SSMA's default mapping is a trap. SSMA converts VARCHAR2(n) → varchar(n) by default. If your Oracle DB uses AL32UTF8, that Unicode data is preserved only if you either change the mapping to nvarchar or ensure the target database uses a UTF-8 (_UTF8) collation — otherwise data loss/corruption is likely. Severity: major. — SSMA for Oracle: Project Settings (Type Mapping)

  • Length semantics differ. Oracle's CHAR length semantics count characters; SQL Server's n for varchar counts bytes, and for nvarchar counts byte-pairs (UTF-16 code units). Supplementary/astral characters can overflow declared lengths. — Collation and Unicode support (SQL Server, Azure SQL)

  • UTF-8 collation specifics. With a _UTF8-enabled collation (SQL Server 2019+, available in Azure SQL), char/varchar store Unicode as UTF-8, while nchar/nvarchar remain UTF-16. Characters outside the basic multilingual plane need 2–4 bytes in UTF-8 vs. two 16-bit units in UTF-16 — so declared column lengths may need widening versus Oracle CHAR semantics. Severity: minor. — same source.

  • Collation is a one-time, up-front decision. Azure SQL Database has database/catalog collation fixed at creation; Azure SQL Managed Instance has an instance-level collation (defaults to SQL_Latin1_General_CP1_CI_AS) that cannot be changed after creation, plus per-database/per-column collations. You must pick a _UTF8 or nvarchar strategy, and case/accent sensitivity, before provisioning. — T-SQL differences between SQL Server and Azure SQL Managed Instance, Collation and Unicode support

Recorded dispute: "Is varchar(n) a safe default for VARCHAR2 columns?"

  • Claim A (Microsoft, via SSMA docs): the default varchar(n) mapping only preserves Unicode if the target uses a UTF-8 collation or you switch to nvarchar.
  • Claim B (Microsoft, via collation docs): even under UTF-8 collation, supra-basic-plane characters need more bytes, so lengths may need widening.
  • Resolution: Decide the Unicode strategy before conversion — either create the target with a _UTF8 collation and keep varchar (sizing n in bytes with headroom), or switch SSMA's mapping to nvarchar (accepting ~2x storage for Latin-heavy data). Re-derive column lengths from Oracle's actual NLS_LENGTH_SEMANTICS (byte vs. char), not by copying the declared n. — Collation and Unicode support (SQL Server, Azure SQL)

Gaps: No caveat/mapping specific to VARCHAR2 on Azure SQL Managed Instance exists in the dataset (only the Azure SQL Database mapping carries the SSMA/UTF-8 caveats above); the Managed Instance guidance found is limited to the general character-set/collation mapping. No mapping to Oracle Database@Azure or Oracle-on-VM was found for this feature (only Azure SQL DB and Azure Database for PostgreSQL Flexible Server had mappings).

GROQ + Knowledge Base

The agent with both endpoints.
smaller model · 9 turns · 43 s · 109.8k in / 3.6k out

✓ verdict ✓ grounded ✓ cited

Grader: BYTE default, varchar/nvarchar units, SSMA default, UTF-8 option; cited

8 tool calls
  1. initial_context on groq · 4,126 chars back
  2. initial_context on kb · 7,679 chars back
  3. knowledge_base_read on kb · 5,206 chars back
    {
 "knowledgeBase": "kb6MdvV3ZA3Q",
 "paths": [
  "character_set_conversion"
 ]
}
  4. groq_query on groq · 752 chars back
    *[_type=="oracleFeature" && (category=="datatype") && (name match "*VARCHAR*" || name match "*NLS*" || name match "*NCHAR*" || name match "*Unicode*" || name match "*character*")]{_id, name, "slug":slug.current, category, introducedIn, deprecatedIn, desupportedIn}
  5. groq_query on groq · 6,688 chars back
    *[_type=="oracleFeature" && slug.current=="varchar2-byte-semantics"]{_id, name, summary, "mappings": *[_type=="mapping" && references(^._id)]{_id, fidelity, effort, rationale, appliesToOracleVersions, "azureTarget": azureTarget->{name, service}, "sources": sources[]->{title, url, publisher}, "caveats": *[_type=="caveat" && references(^._id)]{severity, statement, appliesTo, firsthand, "evidence": evidence->{title, url, publisher}}}}
  6. groq_query on groq · 1,501 chars back
    *[_type=="dispute" && (claimA->_id in ["caveat_sqlserver-utf8-collation-for-varchar","caveat_varchar2-ssma-varchar-default"] || claimB->_id in ["caveat_sqlserver-utf8-collation-for-varchar","caveat_varchar2-ssma-varchar-default"])]{title, whatDisagrees, resolution}
  7. groq_query on groq · 3,125 chars back
    *[_type=="dispute" && _id=="dispute_varchar2-varchar-default-vs-unicode" || _originalId=="dispute_varchar2-varchar-default-vs-unicode"]{title, whatDisagrees, resolution, "claimA": claimA->{statement, severity, "evidence": evidence->{title,url,publisher}}, "claimB": claimB->{statement, severity, "evidence": evidence->{title,url,publisher}}, "resolvedBy": resolvedBy->{title,url,publisher}}
  8. groq_query on groq · 962 chars back
    *[_type=="glossary" && (_id=="glossary_character-set-vs-national-character-set" || _originalId=="glossary_character-set-vs-national-character-set")]{term, "sources": sources[]->{title,url,publisher}}
Answer

Verdict: VARCHAR2(100) with multibyte data maps to Azure SQL Database with partial fidelity (effort M) — the length-semantics mismatch and a tool-default that silently drops Unicode are the two things to watch. [VARCHAR2 byte vs char length semantics mapping to Azure SQL Database — dataset]

Scoped facts (Oracle 11.2–26ai → Azure SQL Database, per mapping_varchar2-byte-semantics--azure-sql-db):

  1. Length semantics differ, not just storage. Oracle VARCHAR2 length is declared in BYTE or CHAR semantics (session default is BYTE; column cap is 4000 bytes, or 32767 with MAX_STRING_SIZE=EXTENDED). SQL Server's varchar(n) counts bytes, nvarchar(n) counts UTF-16 code-pairs — neither matches Oracle CHAR semantics directly, so re-derive n from Oracle's actual NLS_LENGTH_SEMANTICS, don't copy the declared number. — [VARCHAR2 byte vs char length semantics](Sanity dataset, oracleFeature summary)

  2. Major caveat — SSMA's default silently drops multibyte data. SQL Server Migration Assistant (SSMA) for Oracle converts varchar2(n) → varchar(n) by default. If your Oracle DB is AL32UTF8 (multibyte) and the Azure SQL target database isn't using a UTF-8 collation, that Unicode data is preserved only if you change the mapping to nvarchar or set the target to a _UTF8 collation. — SSMA for Oracle: Project Settings (Type Mapping) (Microsoft, severity: major)

  3. Minor caveat — even with UTF-8 collation, widen your lengths. With a _UTF8-enabled collation (SQL Server 2019+, available on Azure SQL), char/varchar store UTF-8 (characters outside the basic plane need 2–4 bytes) while nchar/nvarchar stay UTF-16 (2 code units for those characters). Declared lengths may need to be wider than a straight copy of the Oracle CHAR-semantics length. — Collation and Unicode support (SQL Server, Azure SQL) (Microsoft, severity: minor)

  4. Root cause (why silent loss happens at all). Oracle does not error on a lossy character-set conversion — it transliterates or substitutes a replacement character, and row counts/"success" status won't reveal it. Run a value-level round-trip scan (or Oracle's DMU/csscan) against the source before cutover, and size target columns in characters, not bytes. — "The Migration Completed Successfully. Half the Names Are Now Question Marks." (firsthand) and Choosing a Character Set (Globalization Support Guide 19c) (Oracle)

Recorded dispute: "Is varchar(n) a safe default for VARCHAR2 columns?"

  • Claim A (Microsoft, SSMA docs, major): the default varchar2→varchar mapping only preserves Unicode if the target uses a UTF-8 collation or you switch to nvarchar.
  • Claim B (Microsoft, Collation docs, minor): even under a UTF-8 collation, supplementary characters need extra bytes/code-units, so lengths may need widening.
  • Resolution: Decide your Unicode strategy before conversion — either create the target with a _UTF8 collation and keep varchar (sizing n in bytes with headroom for multibyte characters), or switch SSMA's mapping to nvarchar (accepting ~2x storage for Latin-heavy data). Either way, re-derive column lengths from Oracle's real NLS_LENGTH_SEMANTICS, not the declared number. — resolved by Collation and Unicode support (SQL Server, Azure SQL)

Gaps: The dataset's mapping/caveats above are scoped to Azure SQL Database; I found no separate mapping entry specifically for Azure SQL Managed Instance for this feature — if you're targeting MI, I don't have MI-specific evidence beyond what's shown here for the underlying SQL Server engine's collation behavior.

← q13All casesq15 →