How to Alter or Modify table in Production Environment
Generally when we have to add a column, modify datatype or change column lenght,
we simply write Alter statement. This will work perfectly in any development environment as long as we as developer can revert back to starting point.
However in organization, DBA will ask you to check or if exist condition to any of your DDL scripts. So it's good practice to use
them even in Development environments. Being said that, let's look at how we can use these practice and make a habit of it.
Let's create a dummy table and we will walk through this.
CREATE TABLE dbo.Dummy_Table (
ID int
)
1. Adding a new column to an existing table.
Generally this is a statement which we are all familiar with:
Alter Table dbo.Dummy_Table Add FName Varchar(10)
The above statement will run any environment perfectly if this column does not exist in your table. However a good practice would be to
check if this column exist before we add a new column.
IF COL_LENGTH('dbo.Dummy_Table', 'FName') IS NULL
BEGIN
ALTER TABLE dbo.Dummy_Table ADD FName [varchar](10) NULL ;
END
Now if we re-run a simple statement
ALTER TABLE dbo.Dummy_Table ADD FName [varchar](10) NULL ;
Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'FName' in table 'dbo.Dummy_Table' is specified more than once.
it will throw an error because that column is already existing in given table.
But if we run this statement, if will be always executed without doing any change to actual table
IF COL_LENGTH('dbo.Dummy_Table', 'FName') IS NULL
BEGIN
ALTER TABLE dbo.Dummy_Table ADD FName [varchar](10) NULL ;
END
This way, we will be sure that
script won't fail in production environment and we don't make our DBA mad for giving them a script which can possibly fail.
2. Changing Column length of an existing column in a table
This is a general statement which we use to alter column length.
ALTER TABLE dbo.Dummy_Table ALTER COLUMN FName [varchar](15) NULL ;
Notice that in orginal table we have column length of 10 and now we are increasing to 15. We also know that column already exist in given table.
So we are checking to making sure that column length IS NOT NULL and if that statement is true we can change to any length we want.
IF COL_LENGTH('dbo.Dummy_Table', 'FName') IS NOT NULL
BEGIN
ALTER TABLE dbo.Dummy_Table ALTER COLUMN FName [varchar](15) NULL ;
END
