Modify Columns

Use alter table to modify an existing column. You can modify any number of columns in a single alter table statement.

For example, this command changes the datatype of the type column in the titles table from char(12) to varchar(20) and makes it nullable:

alter table titles
modify type varchar(20) null

• Convert Datatypes
  You can convert only datatypes that are either implicitly or explicitly convertible to the new datatype, or if there is an explicit conversion function in Transact-SQL.
• Modifying Tables and Using Bulk Copy
  Modifying either the length or datatype of a column may prevent you from successfully using bulk-copy to copy in older dumps of the table.
• Decreased Column Length May Truncate Data
  If you decrease the length of a column, make sure the reduced column length does not result in truncated data.
• Modify datetime Columns
  If you modify a column from a char datatype to datetime, smalldatetime, or date, you can neither specify the order that the month, day, and year appear in the output, nor specify the language used in the output. Instead, both of these settings are assigned a default value.
• Modify the NULL Default Value of a Column
  If you are changing only the NULL default value of a column, you need not specify the column’s datatype.
• Check Columns That Have Precision or Scale
  Before you modify a column’s scale, check the length of your data.
• Modify text, unitext, and image Columns
  You can modify the text, unitext, and image columns with certain restrictions.