10 Example of ALTER Clause in SQL

ALTER clause is one of the DDL commands which allows you to change object structure  in database like  table, stored procedure, trigger, or any other object in the database. By using ALTER command you can add or remove columns in a table, you can add and remove index from a table as well as you can update a stored procedure.  ALTER command is an essential SQL commands for programmers similar to SELECT, UPDATE, and DELETE. If you know how to create a table or index then you should also know how to modify a table or index using ALTER command. In this article, we'll see some common ALTER table examples in SQL to learn this command better and become a better database developer. 

10 ways to use ALTER command in SQL

Here are different ways to use ALTER command in SQL to modify table or stored procure in database. Each example will tell you a particular job like adding or removing new columns, index and more. 

1. How to add a new column in SQL Server database

You can use ADD keyword with ALERT command to add a new column in SQL. 

$ ALTER table Books ADD Rating INT;

You can also add multiple columns using ATLER command to an existing table in a single SQL query as shown below:

ALTER TABLE  Book 
ADD column topic VARCHAR(20) NULL, pages INT NULL ;

2. How to remove an existing column in SQL Server

You can remove an existing column in SQL server using DROP clause with ALTER command in SQL as shown below:

$ ALTER table Books DROP COLUMN Rating;

but if the column is not null and associated with any other constraint, you won't be able to drop it, in that case, you first need to drop that constraint object itself before dropping the column. We'll see one example of that incoming section. 

3. How to rename a column in SQL Server?

4. How to increase the length of an existing VARCHAR column?

5. How to add a new column with a default value and NOT NULL constraints?

6. How to add a new column with constraint in SQL Server?

7. How to add more than one column in the existing table in SQL Server?

8. How to drop a NOT NULL column from an existing table in SQL Server

In order to remove a NOT NULL column from a table in SQL Server, you first need to drop the constraint then drop the table after that you can drop the column like before as shown in the second example. 

ALTER TABLE Books DROP CONSTRAINT DF__Boo__Sourc__28063D95

ALTER TABLE Books DROP COLUMN [Source]

9.  How to add a new constraint in a table?

Adding a constraint is not very different from adding a column in SQL. Just use ALERT command with ADD CONSTRAINT clause to add a new constraint like UNIQUE in SQL Server

ALTER TABLE Books ADD CONSTRAINT PK_BookByAuthor PRIMARY KEY (Author,Book);

10. How to remove a constraint from an existing table in SQL?

Just like you removed a column from a table, you can also remove a constraint from the table by using ALTER with DROP CONSTRAINT clause in SQL. 

ALTER TABLE Books DROP CONSTRAINT PK_BookByAuthor 

That's all about 10 Examples of ALTER clauses in SQL. We have learned a lot about how to modify or change existing tables in a database like adding new columns, removing an existing column, adding columns with constraints, modifying existing columns. Let me know if you think any useful ALTER command example has been missed here.

Other SQL and Database Articles you may like:

• MySQL vs NoSQL comparison
• How to use Stored Procedure in SQL?
• How to use WHERE and HAVING clause in SQL
• Difference between UNION and UNION ALL in SQL
• What are T-SQL Window Functions?
• 4 Examples of CASE expression in SQL Server
• 10 Examples of ALTER command in SQL
• Difference between Self and Equi join in SQL
• How to find top 10 records in a table?

Thanks for reading this article so far. IF you like it then please share and if you have any questions, feel free to ask.