SQL Interview: #11 Adding a column in the middle of a table's columns

Job Interview

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Medium

Question:

When you add a column to an existing SQL Server table, the column is added to the end of the table. The column will end up with the highest column_id.

If a developer asks you how a column can be added to the middle of a table instead, what is your advice and how would you approach the request?

Answer:

The first part of the advice is that ideally you won't care what order the columns are in your table. However, I completely understand that a developer might like to see columns grouped appropriately in a list of columns for a table, rather than just a random list of columns.

Unfortunately, SQL Server does not have an option like MySQL (for example), where you can add "AFTER", "FIRST", or "LAST" when defining columns.

With SQL Server, the general approach is to:

  • Copy the data out to a temporary table
  • Drop any foreign key constraints that reference the table
  • Drop and recreate the table with the desired order
  • Copy the data back in from the temporary table
  • Recreate any foreign key constraints that reference the table
  • Drop the temporary table

 

 

2 thoughts on “SQL Interview: #11 Adding a column in the middle of a table's columns”

  1. since you are writing about MSSQL:
    instead of copying to a temporary table you can simply sp_rename the original table and drop it later

    thank you for your blog, always a nice read
    regards from F├╝rth/Germany
    Robert

    1. Hi Robert,

      Yes, you can (and I often do), but then it can get messy. You'd also need to drop or rename all primary key constraints, default constraints, unique constraints, foreign key constraints, etc. after you rename the table.

      Otherwise, creating the replacement table would fail.

Leave a Reply

Your email address will not be published. Required fields are marked *