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.

[cc lang=”sql”]
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
[/cc]

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.

17 comments
Çağatay 09 Feb 2017 at 12:30 pm

Hi..I ve got a NOT NULL column, N I want to run ALTER for varchar size changing.if I wont add NOT NULL specification to ALTER ,Will column NOT NULL specification change to NULL ?

Ferdinand Salianon 15 Jan 2016 at 6:13 am

Thank you. sir .. Nice post 😀

hedayat 26 Aug 2015 at 5:52 am

who can make a column as primary key whit alter alter syntax

sonu 01 May 2015 at 11:19 am
Arif Nazem 17 Mar 2015 at 12:12 pm

how to add auto increment feature to an identity(primary key) column through code in sql server

ahsan 25 Apr 2015 at 5:04 am

dear write this code
create table stdacce(

acc_id int IDENTITY(1,1) PRIMARY KEY,
matric varchar (50),
fsc varchar(50),
bsc varchar(50)
);

mohammed 02 Jan 2015 at 6:13 pm

thank u for helping me

jordan 19 Nov 2014 at 6:24 pm

thanks a lot

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

  • Thanks for visiting!
css.php