Alter Table Alter Column

The Alter Column statement can modify the data type and the Nullable attribute of a column. The syntax is the same for SQL Server 2005 and SQL Server 2008 except 2008 allows the sparse attribute to be changed.

For the example below, we will begin by creating a sample table, then we will modify the columns.

CREATE TABLE dbo.Employee
(
EmployeeID INT IDENTITY (1,1) NOT NULL
,FirstName VARCHAR(50) NULL
,MiddleName VARCHAR(50) NULL
,LastName VARCHAR(50) NULL
,DateHired datetime NOT NULL
)

-- Change the datatype to support 100 characters and make NOT NULL
ALTER TABLE dbo.Employee
ALTER COLUMN FirstName VARCHAR(100) NOT NULL

-- Change datatype and allow NULLs for DateHired
ALTER TABLE dbo.Employee
ALTER COLUMN DateHired SMALLDATETIME NULL

-- Set SPARSE columns for Middle Name (sql server 2008 only)
ALTER TABLE dbo.Employee
ALTER COLUMN MiddleName VARCHAR(100) SPARSE NULL

Columns can be altered in place using alter column statement.

  • Only the datatype, sparse attribute (2008) and the Nullable attribute of a column can be changed.
  • You cannot add a NOT NULL specification if NULL values exist.
  • In order to change or add a default value of a column, you need to use Add/Drop Constraint.
  • In order to rename a column, you must use sp_rename.
Column ordinals cannot be altered using this alter table. To alter ordinals, use Managment Studio and right click design via the GUI.

9 comments
poonam yadav 30 Sep 2014 at 9:48 am

very useful for me

prashant 10 Mar 2014 at 8:36 pm

thanks my DD

Ronald Hofmann 26 Jan 2014 at 4:28 am

Hi,
very interesting :)

I tried to change a column name like this (which didn´t work btw):
ALTER TABLE MutterUnterPos ALTER COLUMN geaendert_am CreatedDate TIMESTAMP NOT NULL

Any idea what I´m doing wrong?

Greetings from Switzerland, Ronald Hofmann
— 

HUGO 12 Feb 2014 at 8:32 pm

Which one is the field to be modified???? Is it “geaendert_am” or “CreatedDate”?
By the way also che that the updated field does not have a null value since one of the constraint you are setting is that it cannot be null.

Justin 10 Apr 2014 at 10:16 am

You can’t rename a column using ALTER TABLE ALTER COLUMN.
You need to use:

sp_rename ‘TableName.ColumnName’, ‘NewColumnName’, ‘COLUMN’

ali 06 May 2013 at 4:32 pm

thank you
that was very needy

Imran 18 Apr 2013 at 9:35 pm

thank u for helping me

Julekha 22 Mar 2011 at 2:02 am

Can you please tell me how can I add constraint to a column after creating the table…

Derek Dieter 02 Sep 2010 at 8:48 am

Hi Amy, sure. To do this you need to actually update the column like this:
UPDATE T
SET YOURCOLUMN = ‘prepend’ + YOURCOLUMN + ‘postpend’
FROM yourtable T

I would try this in a Dec environment first. Also, be aware that this will update all the rows in the table unless yo use a WHERE clause

Featured Articles

 Site Author