How to Alter Tables and Modify Columns in SQL Server

In this tutorial we will show how to use the ALTER TABLE statement to modify columns in a table. We can either add, delete, or modify columns in an existing table. This is especially useful when working with large databases, making possible lengthy tasks quick and precise.

Setting Up

Before we alter a table we must first create one and insert values into it. We will create a table called Persons with columns of P_Id, LastName, FirstName, Address, and City.

 

 

Next we will insert entries into the table with the INSERT statement.

 

 

Add a Column to a Table

We will add a column called ‘DateOfBirth’ into the table. We will use the Add statement and add a desired column name along with a data type.

 

 

If you refresh the object explorer, you will notice the new DateOfBirth column in the Persons table or simply run a query to see the changes. Also notice how DateOfBirth is of datetime data type, we will change it in the next example.

Change a Column Data Type in a Table

We will change the data type of DateOfBirth from datetime to nvarchar. We will use the ALTER COLUMN statement to change the data type of DateOfBirth.

 

 

If you refresh the object explorer, you will see that the data type of DateOfBirth is now nvarchar(30):

Drop a Column in a Table

Finally, we will drop the DateOfBirth column we added earlier. The syntax is similar to previous example, but instead uses the DROP statement to let SQL Server know that the specified column is to be dropped from the table.

 

 

By using of the DROP statement, we are able to drop the DateOfBirth column.


As you can see, the table looks as if we did not add a DateOfBirth column. The ALTER TABLE statement helps with mistakes made in the table or extra columns that need to be added.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *