I learned something new about databases this week. Perhaps I should have already known this, but it surprised me, so I have decided to share with the small segment of the world that reads this blog. In the past, I’ve always done database table management (i.e. creating/dropping tables or columns) through a GUI (like Management Studio for SQL Server, or phpMyAdmin for MySQL). In these tools, it is very easy to insert a new column in the middle of a table, and it never occurred to me that this was a big operation. This week, I had to write a script that would perform my changes to a table on a customer database. It turns out that the only way to put add a new column to the middle of a table1 is to:
Create a temporary table, with the columns in the order you want.
Insert everything from your original table into the new table.
Drop the original table.
Rename the temporary table to the name of the original table.
Recreate any indexes and constraints that existed on the original table.
On a very large table, this is quite a lengthy operation! But if you just add the new column to the end of the table, you can do that in a single SQL statement, and it runs quite quickly. And the fact is that if your code relies on the columns being in a particular order, you are doing something wrong. (Fortunately, I knew enough that I got that part right.)