NAME

DBIO::MSSQL::Diff::Column - Diff operations for MSSQL columns

VERSION

version 0.900007

DESCRIPTION

Column-level diff operations for MSSQL. MSSQL supports ALTER TABLE ADD/DROP COLUMN, ALTER COLUMN for type/nullability/default changes. Built on DBIO::Diff::Op (the nested create/change/drop walk) and DBIO::Diff::Compare (the changed_column_fields sameness predicate).

ATTRIBUTES

lossy_pairs

my %pairs = %DBIO::MSSQL::Diff::Column::LOSSLESS_PAIRS;

The changes of data_type that SQL Server carries out without loss, as the old->new keys "column_change_lossy" in DBIO::Diff::Compare reads: the integer types to a wider one (tinyint < smallint < int < bigint) and date to datetime2. The string types of one family (char to varchar, nchar to nvarchar, char to nchar, varchar to nvarchar, binary to varbinary) are lossless as long as the length does not shrink ("lossy"). Every other pair of types is lossy, in doubt it is: datetime to datetime2 exposes the 3.33 ms steps of the old type, a number to a string rewrites it.

METHODS

diff

lossy

True for an alter op that can alter or lose stored values, so that "apply" in DBIO::Deploy::Base runs it only with allow_destructive. The answer is "column_change_lossy" in DBIO::Diff::Compare over the types and lengths of the two columns, with the pairs of "lossy_pairs"; a length of max (-1) is longer than every number. An op whose rendered type does not change (only nullability or the default) keeps every value and is not lossy; NOT NULL over a NULL is refused by "_refuse_unfillable" in DBIO::MSSQL::Deploy. Other actions are not lossy.

retypes

True for an alter op whose rendered type differs from the live one, a width change included. SQL Server refuses such an ALTER COLUMN while a foreign key stands on the column, as the child column or as the referenced one (measured 2026-10-21, widths of nvarchar too; a nullability change alone runs), so DBIO::MSSQL::Diff drops and creates again the foreign keys of the column.

unbinds_dependents

True for an alter op the server refuses while an index or unique constraint covers the column: the column becomes NOT NULL or changes its type beyond a width change of nvarchar/varchar/varbinary (the cases measured to work). Anything not known to work counts as true.

as_sql

summary

ALTER COLUMN IS ONE STATEMENT

An alter op renders one ALTER COLUMN carrying the fully rendered type and an explicit NULL / NOT NULL, never a type statement and a nullability statement side by side. Three facts from the Transact-SQL ALTER TABLE grammar force that shape:

  • ALTER COLUMN column_name type_name [ ( precision [, scale] | max | ... ) ] [ COLLATE ... ] [ NULL | NOT NULL ] [ SPARSE ] -- the type is not optional in that production, so a bare ALTER COLUMN a NOT NULL is not a statement the server can parse.

  • "If you specify NULL or NOT NULL with ALTER COLUMN, you must also specify new_data_type [(precision [, scale])]. If the data type, precision, and scale aren't changing, specify the current column values." So repeating an unchanged type is not redundancy, it is what the grammar asks for.

  • "ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable." Leaving the nullability off an ALTER COLUMN does not preserve it -- it drops it. Writing it every time is the only way a type change keeps a NOT NULL.

The trigger for that statement is the rendered type ("_mssql_column_type" in DBIO::MSSQL::DDL: length, precision/scale and (max) included), not the bare data_type string. INFORMATION_SCHEMA keeps the width in CHARACTER_MAXIMUM_LENGTH, so nvarchar(64) and nvarchar(128) share a data_type: comparing that string alone recognised no change and returned the empty statement, while the comparator went on reporting one, and the diff never converged. Comparing the rendered type also means two spellings of one native type (varchar and nvarchar both render nvarchar here) produce no statement at all.

A column whose rendered type and nullability both stand still gets no ALTER COLUMN; a default-only change emits only the constraint DDL below.

DEFAULTS ARE CONSTRAINTS

There is no ALTER COLUMN ... SET DEFAULT / DROP DEFAULT in this dialect -- that is the ANSI/PostgreSQL spelling. A default is an object:

ALTER TABLE t ADD CONSTRAINT DF_t_a DEFAULT ((0)) FOR a;
ALTER TABLE t DROP CONSTRAINT DF__t__a__3B75D760;

New constraints are named DF_<table>_<column> rather than left to the server, so a later diff can address them without introspecting first. Dropping one needs the live name, which the model carries as default_constraint_name (see DBIO::MSSQL::Introspect::Columns); without it there is no correct statement to emit and this class refuses (throws a DBIO::Exception) rather than emit a plausible-looking one that removes nothing.

A default the target no longer declares (default_value undef) is removed: core's comparator reports it and the op renders only the DROP CONSTRAINT by the live name. A live DEFAULT NULL counts as no default and is left alone. Dropping a whole column needs no such statement from this class; the removal of a default is an alter op.

When a default is replaced alongside a type change, the DROP CONSTRAINT is ordered before the ALTER COLUMN and the ADD CONSTRAINT after it, because SQL Server refuses to change the data type of a column that is "associated with a default definition".

THE DESIRED-STATE CONTRACT APPLIES TO RENDERING TOO

as_sql reads new_info the way "changed_fields" in DBIO::Diff::Compare reads it when deciding this op exists at all: a field the target leaves undef was not prescribed, so the live value stands. That is applied per field, not per hashref -- a target naming a type but no size keeps the live length, because a bare nvarchar in a column definition is not "unbounded", it is nvarchar(1).

Two consequences worth stating plainly:

  • A target that does not prescribe not_null keeps the live nullability instead of defaulting to nullable.

  • "This column should have no default" is expressible: the comparator reports a live default the target leaves undef as changed, and the op drops the live constraint (see "DEFAULTS ARE CONSTRAINTS").

DEPENDENT INDEXES AND UNIQUE CONSTRAINTS

Measured live (2026-10-20, MSSQL 2022): ALTER COLUMN fails ("The object 'UQ_t' is dependent on column 'uq'", "The index 'ix1' is dependent on ...") on a column in a unique constraint, a unique index or a nonclustered index when the column goes from NULL to NOT NULL or changes its type. These work: NOT NULL to NULL, a width change of nvarchar/varchar/varbinary, and a nullability change of a foreign key column. Any retype of a foreign key column, a width change included, is refused while the key stands ("The object 'FK' is dependent on column", on the child column and on the referenced one; "retypes"); the foreign key phases of DBIO::MSSQL::Diff handle that. "unbinds_dependents" says whether an alter op is one of the failing kind; DBIO::MSSQL::Diff then drops the indexes and unique constraints of the column before the column phase and creates them again after it.

LIMITS

Every statement here is written against the documented grammar; none of it has been executed against a server (this driver's live suite does not exercise the introspect/diff path). What a live run would still have to confirm: that sys.default_constraints reports the names as assumed, and that the emitted statements are accepted in the order they are emitted.

Independent of spelling, SQL Server refuses ALTER COLUMN on a column that other objects depend on -- one used in a PRIMARY KEY / FOREIGN KEY, in a CHECK or UNIQUE constraint, in an index (beyond a plain width change), or associated with a default definition. This class removes the default dependency only when it is replacing that default; indexes and unique constraints are handled by DBIO::MSSQL::Diff (see above); the others it does not address at all, and a diff that runs into one fails loudly at the server.

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.