Alter Table Alter Column
-
Posted on July 14, 2009 by Derek Dieter
-
7
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.
- Comments (RSS)
- Trackback
- Permalink
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 ?
Thank you. sir .. Nice post 😀
who can make a column as primary key whit alter alter syntax
nice post
how to add auto increment feature to an identity(primary key) column through code in sql server
dear write this code
create table stdacce(
acc_id int IDENTITY(1,1) PRIMARY KEY,
matric varchar (50),
fsc varchar(50),
bsc varchar(50)
);
thank u for helping me
thanks a lot
very useful for me
thanks my DD
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
—
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.
You can’t rename a column using ALTER TABLE ALTER COLUMN.
You need to use:
sp_rename ‘TableName.ColumnName’, ‘NewColumnName’, ‘COLUMN’
thank you
that was very needy
thank u for helping me
Can you please tell me how can I add constraint to a column after creating the table…
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
