SQL Alter

This page covers the implementation of SQL alter (modifying) tables.

Often, tables need changes after creation. In SQL, such modifications happen through the alter statement.

The syntax for altering tables is easy. It contains several methods, with each operating different functions. The methods within this tutorial are:

All of the above methods operate through the “alter statement“.

Let’s use the previous database “students”, with the table “student_names”.

The table contains 4 columns: id (int), firstName (varchar(50)), lastName(varchar(50)), and age(int).

Add columns

Adding columns occurs through the alter statement. The keywords are “alter table” and “add“.

The following example shows how to add columns in a SQL table.

ALTER TABLE student_names
ADD address varchar(255);

# Output: Commands completed successfully.

Do not forget to include the datatype after the name of the new column. Now let’s show the table and see the changes.

SELECT * FROM student_names;

id	firstName	lastName	age	address

Dropping columns occurs through the alter statement. The keywords are “alter table” and “drop column“.

The following example shows how to drop (delete) columns in a SQL table.

ALTER TABLE student_names
DROP COLUMN address;

# Output: Commands completed successfully.

Let’s see the modified table.

SELECT * FROM student_names;

id	firstName	lastName	age

Altering columns occurs through the alter statement. The keywords are “alter table” and “alter column“.

The alter column method is used to change the datatype of the column.

The following example shows how to alter (modify) columns in a SQL table.

ALTER TABLE student_names
ALTER COLUMN firstName varchar(100);

# Output: Commands completed successfully.

Now the student first name can contain up to 100 characters (50 previously).

Renaming columns cannot occur through the alter statement in SQL Server.

Alternatively, the platform has the “sp_rename” function. The syntax is very easy and straightforward.

Please note that using “sp_rename” is not recommended. Instead, you can drop the column and recreate it.

The following is an example of how to rename columns in a SQL table.

sp_rename 'student_names.age', 'studentAge';

# Output: Caution: Changing any part of an object name could break scripts and stored procedures.

Now let’s output the table again and see the result.

SELECT * FROM student_names;

id	firstName	lastName	studentAge