Join Class

Alter Table in SQL Database

Altering a table in SQL, means, making changes in table structure, like changing a column name, adding a new column, deleting a column or adding a constant etc.

Sometimes, we have to make changes in SQL table with data, means we don't need to delete the data for making changes, however, if you make changes in column data type, then make sure the new data type is compatible with existing data in that column.

For example, if some column has numeric data like 10, 20, 15, 10.25 etc, and you try to make that column as Varchar, nvarchar, string etc, that will not allow you, will throw error.

Here you learn alter table statement in sql server, and how to alter column in sql table.

SQL Alter Table Syntax Example

(Note: In this tutorial i am not talking about updating data in SQL table, talking about altering / updating SQL Table structure). The SQL Server (Transact-SQL) ALTER TABLE statement is used to add, modify or drop columns in a table.

How to add a new column in (Alter) Existing SQL Table

Alter table statement in sql server, In case you want to add a new column in your existing SQL Table.

Alter  TABLE [tableName]            
Add  ColumnName nvarchar(1000)
How to change a column name in (Alter) Existing SQL Table

Alter TABLE, Change column name in SQL Table, To change the name of the column in some particular table we will be built-in SQL store procedure “sp_rename” , here is an example.

sp_rename '[tableName].ColumnName', 'NewColumnName', 'COLUMN';

Note: After changing Column name in SQL Table may break your scripts and stored procedures, views, function, triggers. So, Remember to change them in all other places

How to drop a column in (Alter) Existing SQL Table

In case you want to drop a column from SQL table permanently

ALTER TABLE [tableName]            

Once you drop the column all data in that particular column will be lost permanently

How to rename a Table in SQL Server

Remember, You can not use the ALTER TABLE statement in SQL Server to Rename a SQL Table, there is a system stored procedure called "sp_rename" to do that job.

Here is the syntax to Rename a table in SQL Server.

 sp_rename 'old_table_name', 'new_table_name';

Note: even after changing the table name, all constraint names like primary key, foreign key will remain the same

Hire SQL Developer
Alter Table in SQL Database
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
MS SQL Examples | Join MS SQL Course