ALTER TABLE in SQL Server

The below SQL Tutorial explains all the basic syntactical ALTER statements that we can perform in SQL like Add Column, Alter Column, Rename Column, Drop Column etc..

DescriptionThe SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Adding a column
ALTER TABLE table_name
ADD column_name column-definition;
Ex:
The following statement will add a column "EMPName" in the Employee table which has a size of 50 characters.

ALTER TABLE Employee ADD EMPName VARCHAR(50)
GO

Adding a column with default value:
The following example adds a column that allows null values and has no values provided through a DEFAULT definition. In the new column, each row will have NULL.

ALTER TABLE dbo.Employee ADD EMPName VARCHAR(50) NULL ;
GO

Adding a column with UNIQUE constraint:
The following example adds a new column with a UNIQUE constraint.

CREATE TABLE dbo.Employee (column_a INT) ;
GO
ALTER TABLE dbo.Employee ADD EMPName VARCHAR(50) NULL
CONSTRAINT empname_unique UNIQUE ;
GO

To view the schema:

EXEC sp_help Employee ;
GO

Adding a column with CHECK constraint:
The following example adds a constraint to an existing column in the table. The column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added.

CREATE TABLE dbo.Employee ( column_a INT) ;
GO

INSERT INTO dbo.Employee VALUES (-1) ;
GO

ALTER TABLE dbo.Employee WITH NOCHECK
ADD CONSTRAINT emp_check CHECK (column_a > 1) ;
GO

EXEC sp_help Employee;
GO

The WITH NOCHECK option will ignore validating the existing records with the constraint that we are creating.

Adding a column with default constraint:
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column with value 50.

CREATE TABLE dbo.Employee ( EmpID INT, EMPName VARCHAR(50)) ;
GO
INSERT INTO dbo.Employee (EmpID)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.Employee
ADD CONSTRAINT emp_def
DEFAULT 'ABCEmployee' FOR EMPName ;
GO

Once the constraint has been created insert a new record without specifying a value for EMPName where the default constraint has been created.

INSERT INTO dbo.Employee (EmpID) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.Employee ;
GO

You will see the ABCEmployee is being inserted to the new record as a default value.

Adding nullable column with default values:
The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

CREATE TABLE dbo.Employee ( column_a INT) ;
GO
INSERT INTO dbo.Employee VALUES (1) ;
GO

ALTER TABLE dbo.Employee
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.Employee ;
GO


Modifying an existing column

ALTER TABLE table_name
  ALTER COLUMN column_name column-definition;

Ex:
ALTER TABLE Employee ALTER COLUMN EMPName VARCHAR(100)

 The above statement modify the size of the existing column "EMPName" from 50 to 100 characters.


Drop an existing column
ALTER TABLE table_name
  DROP COLUMN column_name;

Ex:
ALTER TABLE Employee DROP COLUMN EMPName

The above statement will drop the column "EMPName" from Employee table.