The syntax to add, drop, or modify a column to include user-defined datatypes is the same as with a system-defined datatype.
alter table titles add newcolumn usertype not null
The NULL or NOT NULL default you specify takes precedence over the default specified by the user-defined datatype. That is, if you add a column and specify NOT NULL as the default, the new column has a default of NOT NULL even if the user-defined datatype specifies NULL. If you do not specify NULL or NOT NULL, the default specified by the user-defined datatype is used.
You must supply a default clause when you add columns that are not null, unless the user-defined datatype already has a default bound to it.
If the user-defined datatype specifies IDENTITY column properties (precision and scale), the column is added as an IDENTITY column.
To modify the au_lname of the authors table to use the user-defined newtype datatype:
alter table authors modify au_lname newtype(60) not null
If you do not specify either NULL or NOT NULL as the default, columns use the default specified by the user-defined datatype.
Modifying the table does not affect any current rules or defaults bound to the column. However, if you specify new rules or defaults, any old rules or defaults bound to the user-defined datatype are dropped. If there are no previous rules or defaults bound to the column, any user-defined rules and defaults are applied.
You cannot modify an existing column to an IDENTITY column. You can only modify an existing IDENTITY column with user-defined datatypes that have IDENTITY column properties (precision and scale).
Drop a column with a user-defined datatype in the same way that you drop a column with a system-defined datatype.