MSSQL Workarounds and suggestions when change types

[fbuccioni]

I have done some things on an existing database and other in yet managed database.

When a type is changed.

When I change types the default constraint is the most annoying part of the problem, indeed I open a Issue #1744, and post a patch but check for type changes to auto drop the default constraint but it was ignored, anyway I solve by forcing change of default value but was a headache in a large set of changes, anyway there is a lot more to do before a type change.

I have documented the process that works for me to know how to solve it and is the following.

1. If have indexes associated to the column
   1.1. Needs to be dropped before the alter column
   1.2. Needs to be recreated after the alter column
2. If have constraints, have to drop it before
   2.1. Default constraints
   2.2. Foreign keys
   2.4. If enum with constraint checks
   • Needs to remove constraint checks (CK_ prefix)
3. If have unique keys, must be dropped before, in alter column will be recreated

To perform all this fixes, I suggest to reorder of the operations in the following order

1. Drop
2. Alter
3. Recreate (in case of indexes)

To achieve some tasks I have done the following helper module (Includes drops associated keys only for the column)
mssql.py

Support for native JSON

(Issue request in #13212) SQL Server since 2025 (17.x) supports native JSON type

Hope this could contribute to a better software, any question are welcome.

PS: I have to mention and thanks to ChatLPO that allows me to post this suggestions based on the work in the company.

[CaselIT]

Hi

When I change types the default constraint is the most annoying part of the problem, indeed I open a Issue #1744, and post a patch but check for type changes to auto drop the default constraint but it was ignored

Why do you say it was ignored? it was released in version 1.17.2. If something else does not work, please open an issue with a reproducing case, thanks.

Support for native JSON

This would be a sqlalchemy feature. it seems that there is no issue it, so I created sqlalchemy/sqlalchemy#13212

[fbuccioni]

Hi CaselIT

Why do you say it was ignored? it was released in version 1.17.2. If something else does not work, please open an issue with a reproducing case, thanks.

About this indeed some changes was done, but the part of the patch that I post which checks for type change was ignored, but anyway is irrelevant at this point because in the list after that comment which have more things to have in mind to write a neat MSSQL migration with type changes.

This would be a sqlalchemy feature. it seems that there is no issue it, so I created sqlalchemy/sqlalchemy#13212

This is awesome, thanks I post my little trick to make it work

[CaselIT]

About this indeed some changes was done, but the part of the patch that I post which checks for type change was ignored,

the implementation that was merged seems equivalent to your patch, so it should fully cover that use case..