One of the main problems we seem to be stuck with at this point in programming history is, how do we version our databases?
You could put the whole database into version control, I guess. But that could end up being a lot of data to create a delta for each time.
You could just version the structure. But then you are left with importing data from your current system, which may or may not work.
So most shops either ignore the problem entirely (not a very good idea, if you ask me), or they require every structural change to be recorded in a SQL script so that by running a series of scripts, you can recreate the database to any point in time.
If you are working on a commercial project, or any other project that has to be deployed to multiple locations, you need to at least create delta SQL scripts so that the new location can get the new data.
And creating these delta scripts isn’t as hard as it seems, once you learn the basics. But it would be nice if someone created a more elegant solution.
There are several things you might need to change in your database as time passes, but the one you will probably need to do the most is add, remove and modify fields/columns. You do this with the ALTER TABLE command.
If all you need to do is to add a column to your table, the command is quite simple and looks a lot like the CREATE TABLE statement we looked at last week.
ALTER TABLE tableName ADD newField datatype(size,precision) etc...
To remove a field, you use the DROP statement
ALTER TABLE tableName DROP newField
To change a field definition
ALTER TABLE tableName ALTER COLUMN newField dataType(size,precision) etc...
As far as I know, there is no clean SQL way to change a column name. You’d have to
- ADD a new field with the new name and type information
- Issue an UPDATE command to move the data from one field to the other
- DROP the old field.
Many times we’d like to ADD several fields, drop several fields and change the data type information all at one time. This is where most of the documentation you can find online for the ALTER TABLE command falls short.
To Add multiple columns:
ALTER TABLE tableName ADD newField datatype(size,precision) etc... , newField2 datatype(size,precision) etc...,
To drop multiple columns:
ALTER TABLE tableName DROP newField, newField2
So you’d think you could change multiple columns the same way, right?
To alter multiple columns, you will need to issue multiple ALTER TABLE statements.
You also cannot ADD and DROP in the same ALTER statement. You will need one ALTER TABLE statement to ADD new columns and another ALTER TABLE statement to DROP all of your columns and yet another ALTER TABLE statement for each column you want to modify.
This isn’t the only reason you wonder what the SQL guys were smoking when they created this syntax. But I’m sure there was a good reason for this at the time. Any SQL guys want to enlighten us?
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding a String - September 3rd, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- SQL WHILE - SQL For Programmers - October 2nd, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- 28 October 2008 at 4:10am
- Recent Links Tagged With "precision" - JabberTags
[...] public links >> precision SQL For Programmers - ALTERing the TABLE Saved by ...