AzureFeeds

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:

You need to add a new column to a table.

A developer insists that the column needs to be inserted into the middle of the existing table, not as a new column at the end.

What should you do?

Answer:

First, you should explore the reasons for this request. It really should not matter where the column is added. When this occurs, it indicates that some sort of dependency is being taken on the order of the columns in the table.

There are many reasons someone might want this, such as they have INSERT statements without column lists. That’s not desirable anyway.

One reason that I have some sympathy with, is that they just want the columns to be in logical groupings within the table design. Unfortunately, SQL Server doesn’t allow this, even though it would seem to be fairly straightforward to implement, and it’s a common request.

Second, if you really need it to happen, you need to migrate the data to a new table that’s designed the way you need it to be. That might be as simple as renaming the original table, creating the new table, and copying the data. But this can be complicated once you have foreign keys, etc. in place.

The post SQL Interview: #29 Add column to middle of SQL Server table appeared first on The Bit Bucket.