SQL Server – How to add column in table

For adding column in table of sql server, We can make use of alter table statement  i.e  alter table [tablename] add [columnname] datatype.  Here datatype could be anything like char, int, varchar, nvarchar that is purely depends upon data that will store in column.

Lets take a scenario to understand how to alter table on UAT server.

In our organization, any changes which are implemented or going to implement on production server will go through change meeting approval by managers. SQL developer use to raise request to add columns in tables on UAT server that contain more than 3 billion of records.

So it was obvious that if we are performing alter column operation on large data table then definitely it will going to take some time to execute and it will affect all the records in the table.

Change request raise for the UAT and assigned to DBA team to perform the task. We planned to follow below steps

1. Before applying changes to the table first thing to take backup of the existing table to avoid data loss.

Select * into emp_details_bkp from emp_details

2. Now execute the alter command on UAT.

alter table emp_details
add emp_sal varchar(20)

alter table emp_details
add emp_reporting varchar(50)

3.Take the logs details

Finally task completed on UAT now decided to follow same steps on production server as standard practice.

Using UAT we can test the patches in early stage whether it work properly or not. UAT server can acts as secondary server to the production at the end year we performing COB (Continuity of Business) activity.

Always make a good practice to execute any patch first on UAT or testing server that helps you to identify the issue if any.
Enjoy learning !!!!

Leave a Reply




+ 2 = 7