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.
Popular search terms:
i want to modify values of a column of an existing table in sql 2005, can u tell me how to do this?
Worked well . Thanks
Hi, Can we Alter column order with script?
I have found myself at this blog many times and each time it answers my question. Thank you, sir!!
Thanks. saved my time
It’s works on sql server 2008.
Thank you.
i want to Edit column in sql language but i don’t know how to do?
i don’t to Edit one by one COLUMN ?
i want use only one query to edit all column in my one table .
please give some solution about this problem !
thank for your help…!
KHON SOTHEARIT
Good,thank you.
Quiero hacer un alter table a una tabla que contiene una columna number y quiero pasarala a varchar2, pero tiene registros guardados en la tabla.
Hi,
I want to alter the datatype of various columns of a table. Is there a way of doing this?
Hi Sushmitha,
Yes, this Alter Table will do this. You just need to specify the new data type. Make sure however that all the data values existing in the table will be able to conform to that data type, otherwise you will get an error.
Good Luck,
Derek
Can u tell me how to drop a default value for a column without using the default constraint?
can you give an example of storing images in a table and retrieving them?
More accurately staetd…Move a column after some other columnmysql> ALTER TABLE `mytable` MODIFY COLUMN `mycolumn` [mycolumn definition] AFTER `someothercolumn`;Move a column to the first positionmysql> ALTER TABLE `mytable` MODIFY COLUMN `mycolumn` [mycolumn definition] FIRST;
Can you please tell me how can I add constraint to a column after creating the table…
Can you give me an example of a column, say ‘nvarchar(max)’ with some values already in the column. Without losing the existing values, I want to replace existing values with:
“prefixText”++”postfixText”
Is there a way to do this?
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
excelent explication!
Reply to this comment
excelent explication!
Thank you! Any improvements are very welcome. I want to make these examples as complete as possible.