ALTER TABLE syntax for changing column order - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 739788 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 4/29/2012 3:12:02 PM
Access Restriction Public


For a long time there used to be a Connect item for this, but now I only get an error message when I try to access it, so it's time for a new one! By the way, this old item was for quite some the top-voted item in the old Ladybug system which predated Connect.

Today when you use ALTER TABLE ADD to add a column, a new column is always placed as the last column. This is far from often desireable. Often developers and database designers want to keep some logic in a column order, so that related column are close to each other. A standard rule we keep in the system I work with is to always have auditing columns at the end. Furthermore many graphical design tools encourage this kind of design, both bottom-end tools like the Table Designer in SSMS as well as high-end data-modelling tools such as Power Designer.

Today, if you want to maintain column order you have no choice but to go the long way: create a new version of the table and copy over. It takes time, and if not implemented correctly, things can go very wrong.

Requests about putting a column in a certain order is often met with objections that column order does not matter for performance, but this is not about database performance - it's about human performance. You want the database documentation to have the same order as the database, and the documentation is easier to read if order is logical.
Sign in to post a comment.
Posted by Tim Abell on 8/6/2015 at 2:05 PM

Using the GUI tool is unsafe:

* It includes half the damn schema in the script it generates. This is massively unsafe in a fast-moving multi-developer/dba environment and is likely to undo other people's changes or even flat out break things.
* If there's a lot of data in the table you want to re-arrange you could actually kill your production server trying to run the script it generates due to the copying of data it employs.

Your workaround comment is no good for many common scenarios.

Posted by Tim Abell on 8/6/2015 at 1:52 PM 46 upvotes and 4 stars on stackoverflow
Posted by Tim Abell on 8/6/2015 at 1:50 PM
MySQL can do it, why can't you?

It's valuable to stop your schema becoming an unholy mess over time that no-one in their right mind would want to have to maintain. Yet another reason to ditch mssql, as if any more were needed.

WONTFIX is like sticking two fingers up to your customers. Not cool.
Posted by Evaapavel on 6/18/2015 at 1:48 AM
I found a lot of pros and cons at this blog:
It also mentions this particular ticket. This is why I'm linking it back as well.
I think the suggested matter is relevant, useful and necessary to be present in a modern DBMS. Which MS SQL Server certainly is.
Actually I don't understand very well why MySQL has this feature while MSSQL does not.
Posted by Erland Sommarskog on 2/26/2013 at 12:22 PM
I'm afraid that you miss the point in several ways. First of all, the tool you recommend in SSMS is seriously broken, and casual use of it can venture your database integrity.

Next, while I mentioned documentation, when we add new columns elsewhere at the end we do this across the board. We check in the table file into version control, and the database looks the same no matter it is development or production.

Properly implemented, such an operation could be a metadata-only operation, and thus be online, instead of a time-consuming offline operation.

In the past we did not use ALTER TABLE at all, but we have recently improved our routines so that ALTER TABLE can be used when it is possible. But given the very limited functionality of ALTER TABLE that is not often.
Posted by Microsoft on 2/22/2013 at 3:51 PM
Hi Erland. Thanks for your feedback. I know this is not what you are looking for but we do provide a tool for you in SSMS that takes care of column re-ordering ( I do understand you prefer a T-SQL version which would be more powerful than this SSMS tool, but given that there is a workaround and the use case is primarily for documentation we are unable to prioritize this change for the next release.