Developers can modify the data type of a column in Table Designer. When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to modify the data type of a column, they should follow the next steps:
1. In Object Explorer, right-click the table with the columns whose data types they want to modify and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2. Select the column for which they want to modify the data type.
3. In the Column Properties tab, click the grid cell for the Data Type property and choose a new data type from the drop-down list.
The new data type is assigned to the column after developers click outside the grid cell or use the TAB key to move to another grid cell. It takes effect in the database when they save their changes in Table Designer.
Notes:
1. Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. In addition, code and applications that depend on the modified column may fail. These include queries, views, stored procedures, user-defined functions, and client applications. Because these failures will cascade, developers must carefully consider any changes they want to make to a column before making it.
2. When developers modify the data type of a column, Table Designer applies the default length of the data type they selected. Developers must always set the data type length for to the desired value after specifying the data type.