Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING.
The most basic part of a database is a TABLE which consists of COLUMNS. Many databases are works in progress that never ends since business requirements change as the company ages. With that said, changing the structure of a table is not an uncommon DBA task.
We are going to build upon the sample database named [TRAINING] that contains a sample schema named [PUBS]. I noticed that the [PUBLISHERS] table does not contain a zip code column like the [AUTHORS] table. I am going to demonstrate how to ADD, ALTER and DROP a zip code column.
The ADD clause of the ALTER TABLE statement is used to insert a new column into a existing table. It is important to allow NULLS if there are existing records in the table; Otherwise, the SQL command will fail.
If we specify the column to allows nulls, the command works just fine. I choose to store the data as an integer since I want to demonstrate how to change the data type of a column in the next example.
The ALTER COLUMN clause of the ALTER TABLE statement is used to modify the data type of a column. I have decided to store the zip code as a 5 character fixed length string.
The DROP COLUMN clause of the ALTER TABLE statement is used to delete the column. Please note, this command will fail if constraints are defined on the column. Any constraints have to be removed before dropping the column.
To sum up this article, we can ADD, ALTER and DROP columns using the ALTER TABLE statement. It is interesting to note that the word COLUMN is missing from the ADD clause. Do not forget about NULLS and constraints, since they may cause you issues.
I will be talking about DEFAULT constraints in my next article. This will allow you to modify a table to add a column with a NOT NULL clause.
