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.
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?
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