Preserve SQL Server native string types during column expansion

[axellpadilla]

Problem

Master has an opt-in native string type label mapping, but it does not fix column expansion preserving nvarchar / nchar, because nvarchar and nchar are still not treated as string types by is_string(), and expansion still lacks an adapter override that derives the altered column type from the reference column’s resolved SQL Server type.

For example, an nvarchar column may be treated as a generic string and expanded using varchar(...) instead of keeping the original nvarchar(...) type family.

This happens because SQLServerColumn currently handles string types too narrowly:

• is_string() only recognizes varchar and char
• NVARCHAR maps to VARCHAR(8000)
• NCHAR maps to CHAR(1)
• column expansion uses the generic class-level string type instead of the reference column's actual SQL Server type

This can cause incorrect schema changes when models use Unicode string columns.

Expected behavior

SQL Server string type families should be preserved when expanding column types.

Examples:

• nvarchar(100) -> nvarchar(200)
• nchar(10) -> nchar(20)
• nvarchar(...) should not become varchar(...)
• nchar(...) should not become char(...)

Additional widening, such as varchar -> nvarchar, integer widening, or numeric precision/scale widening, should only happen behind an explicit opt-in flag.

Proposed change

Use the approach from this branch as the basis for a PR:

axellpadilla/dbt-sqlserver@master...patched

Relevant parts:

• recognize nvarchar and nchar as string types
• preserve the original string type family when rendering expanded column types
• derive the expanded type from the reference column's resolved type
• keep broader safe type expansion behind an explicit flag, such as sqlserver__enable_safe_type_expansion

Relevant files:

• dbt/adapters/sqlserver/sqlserver_column.py
• dbt/adapters/sqlserver/sqlserver_adapter.py

Model-level guardrail

Column expansion can be expensive or risky on large tables, especially when it requires ALTER TABLE ... ALTER COLUMN.

Add a model config to control whether automatic column type expansion is allowed for a model.

Example:

{{ config(
    column_type_expansion_max_rows=1000000
) }}

Suggested behavior:

• default to existing behavior, or default to disabled if maintainers prefer a safer breaking-change path
• skip automatic column type expansion when the config is disabled
• allow projects to opt in only for models where expansion is safe
• keep this separate from dbt_sqlserver__enable_safe_type_expansion, which controls what type changes are considered safe

This gives users a way to avoid expensive schema changes on large production tables while still allowing controlled expansion where needed.

Related issues

May also address or close:

• "Error 8115 Arithmetic overflow error converting nvarchar to data type numeric" when seeding a numeric column with some empty rows and specifying column_types #425
• Dbt Docs SYSNAME instead of NVARCHAR datatype #637
• Add column does not work for nvarchar #446

Issue #637 reports SQL Server type metadata being surfaced incorrectly as SYSNAME instead of NVARCHAR. While the root cause is different, both issues stem from SQL Server native type handling and preserving correct type identity throughout the adapter.

Suggested tests

• nvarchar is recognized as a string type
• nchar is recognized as a string type
• nvarchar(100) expands to nvarchar(200), not varchar(200)
• nchar(10) expands to nchar(20), not char(20)
• nvarchar -> varchar is not treated as safe expansion
• optional widening behavior remains disabled unless explicitly enabled
• column type expansion is skipped when the model config disables it
• column type expansion runs when the model config enables it