Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "ALTER TABLE ... ALTER COLUMN" - Changing Column Data Type

By: FYIcenter.com

(Continued from previous topic...)

How to change the data type of an existing column with "ALTER TABLE" statements?

Sometimes, you may need to change the data type of an existing column. For example, you want increase the string length of a column. You can use the "ALTER TABLE ... ALTER COLUMN" statements in the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name new_type

Here is a good example of change column data types:

-- Can not make a string column shorter
ALTER TABLE tip ALTER COLUMN subject VARCHAR(10)
GO
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

-- Can make a string column longer
ALTER TABLE tip ALTER COLUMN subject VARCHAR(100)
GO
Command(s) completed successfully.

-- Can not change string to numeric
ALTER TABLE tip ALTER COLUMN subject NUMBER
GO
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The statement has been terminated.

As you can see, the new date type must be compatible with the old data type in order for the "ALTER TABLE ... ALTER COLUMN" statement to work.

(Continued on next topic...)

  1. What is a table?
  2. What are DDL (Data Definition Language) statements for tables?
  3. How to create new tables with "CREATE TABLE" statements?
  4. How To Get a List of All Tables with "sys.tables" View?
  5. How To Get a List of Columns using the "sys.columns" View?
  6. How To Get a List of Columns using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns using the "sp_help" Stored Procedure?
  8. How To Generate CREATE TABLE Script on an Existing Table?
  9. How to create new tables with "SELECT ... INTO" statements?
  10. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
  11. How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
  12. How to rename an existing column with the "sp_rename" stored procedure?
  13. How to rename an existing column with SQL Server Management Studio?
  14. How to change the data type of an existing column with "ALTER TABLE" statements?
  15. How to rename an existing table with the "sp_rename" stored procedure?
  16. How To Drop an Existing Table with "DROP TABLE" Statements?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...