By: Greg Robidoux | Comments (1) | Related: > Database Design
Problem
When designing tables you may not always know what columns need to exist prior to when the table is created. Therefore over time table changes are made where you may add or drop columns. Based on this table modification you may want to have certain columns next to each other for easier management and grouping of like data in the table structure. It doesn't really matter to SQL Server where the column is located, but to us humans it sometimes makes a difference. Dropping a column is not a big deal because the column just goes away, but when adding a column the default process is to add the column to the end of the table. So how can you modify the table structure so all of the like columns are next to each other?
Solution
The short answer is that there is not an easy way to move columns around once the table has been created, but let's take a look at a couple of examples and why this is not that straightforward.
T-SQL
When using T-SQL to add a new column, the new column is always added to the end of the table. Let's take a look at how this works using T-SQL.
| CREATE TABLE dbo.Test1 (column_a1 INT) ; GO ALTER TABLE dbo.Test1 ADD column_b1 VARCHAR(20) NULL ; GO ALTER TABLE dbo.Test1 ADD column_a2 VARCHAR(20) NULL ; GO EXEC sp_help 'dbo.Test1'; |
From the output below we can see the new column is added to the end of table. Also, there is no option for the ALTER TABLE command to specify where the new column should go.
Management Tools
The other approach that you might take is to use Enterprise Manager or SQL Server Management Studio. When moving columns around using the GUI tools you can make the changes and add a column in the middle of the table, but let's take a look at what happens when this is done. Below is a sample trace that captures the events that occur after you press the save button.
As you can see the management tools do the following to get this to work.
- create a temporary table called dbo.Tmp_Test1 with the new structure.
- if there is data in the original table dbo.Test1 this data is inserted into the new temp table dbo.Tmp_Test1 (now you have two sets of the same data)
- the original table dbo.Test1 is dropped
- the new table dbo.Tmp_Test1 is renamed to dbo.Test1
- if the table has indexes all of the indexes are recreated as well (this example did not have any indexes)
So this is not a big issue if the tables are small, but think about the impact this would have if the table had millions of rows of data as well as multiple indexes that would need to be recreated.
Also, this approach is not always possible either. Here is an example of a table that was being changed to insert a new column in the middle of the table. As you can see from the error message this change could not be performed, because the table is being replicated and therefore could not be dropped. Other conditions where this would fail is if there were foreign key constraints referencing the table.
Other Approaches
If you really need to modify the table and move columns around you would need to remove the table from replication, drop the foreign keys and then you can manually go through the same steps that the management tools use. This seems like quite an undertaking to just move the columns around in the table, but it can be done if you really need to.
Summary
As mentioned above SQL Server does not really care where the column is located in the table. This is really an issue that we have when managing our database tables. As long as you specify column names when Selecting and Inserting data this positional information will not be an issue. It does become an issue though when you use SELECT * or insert data into tables without specify the column list. As a best practice it is always better to explicitly list the columns you are dealing with instead of relying on the position of the column for your database operations.
If you really need the columns to be displayed next to each other try using a view over the table instead of modifying the underlying table structure.
Next Steps
- Next time you go to make a table change using the GUI tools keep in mind the potential impact
- When adding a column to the end of the table using the GUI tools SQL Server does not drop and recreate the table. A simple ALTER TABLE statement is used instead.
- Get over having to have your columns in your table all line up next to each other. Database changes happen all of the time and everything can't be perfect.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
