Alter Table Sql Server 2008

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.

For Primary Key and Foreign Key Constraints:

Alter table dbo.TableName add primary key (ID);

Alter table dbo.Table Name Add constraint columnname_id_refs Foreign key ("columnname") References dbo.TableNamewithPrimaryKey ("PrimaryKeyColumnName");