SQL: Alter column size

It could happen that in your SQL Server Database you have to change the size of a column.

To do that you can use the ALTER TABLE statement.

The first thing you should know is its syntax, and then you have to pay attention to things like:

  • Is there an index on that column?
  • Do not truncate column values

Contents for SQL: Alter column size

Syntax

ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [NEW COLUMN SIZE]

You can’t alter multiple columns at once.

Column with a index?

You cannot change the size of a column unless the column type is nvarchar,varchar or varbinary, and the new size is equal or larger then the previous one.

If this is not your case, you have to drop the index, change the size and then add the index back again.

Remember that there is a limitation in the maximum size of a index key: 900 or 1700 in the last editions.

Do not truncate

Changing the size means that you could also be decreasing it.
So, you could have some values larger than the new size. They will be truncated.

Doubts? Feel free to leave a comment!