Modify SQL Server database tables to keep similar columns together

By: Greg Robidoux   |   Updated: 2024-05-30   |   Comments (3)   |   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 Add New Column in Middle of Table

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.

SQL Server Management Studio Add New Column in Middle of Table

The other approach that you might take is to use SQL Server Management Studio. When moving columns around using the GUI 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.

In this example, we have a table named Employee with a first_name and last_name column and we want to add middle_name between these two existing columns.

Right click on a table and select Design.

Select where you want to insert the new column and right click and select Insert Column.

Add the column and data type as shown below.

Don't save the change, but right click anywhere and select Generate Change Script the following SQL script below is generated.

In this script below, we can see SSMS does the following to get this to work.

1. create a temporary table called dbo.Tmp_Employee with the new structure.
2. if there is data in the original table dbo.Employee this data is inserted into the new temp table dbo.Tmp_Employee (now you have two sets of the same data)
3. the original table dbo.Employee is dropped
4. the new table dbo.Tmp_Employee is renamed to dbo.Employee
5. if the table has indexes all of the indexes are recreated as well (this example did not have any indexes)

-- To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Employee
	(
	ID int NULL,
	First_name varchar(20) NULL,
	Middle_name varchar(20) NULL,
	Last_name varchar(20) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Employee SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Employee)
	 EXEC('INSERT INTO dbo.Tmp_Employee (ID, First_name, Last_name)
		SELECT ID, First_name, Last_name FROM dbo.Employee WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT' 
GO
COMMIT

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.

Possible Issues with Adding New Column in Middle of Table

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.

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 want to.

SSMS Prevents Table Structure Change

In SSMS there is a option to not allow you to drop and recreate the tables. If this setting is enabled and we try to modify a table that would require the table to be recreated we get this error.

From the SSMS menus go to Tools > Options > Designers > Table and Database Designers to get this screen and the option that needs to be disabled is highlighted below (Prevent saving changes that require table re-creation).

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