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.


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

Description

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 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 (http://msdn.microsoft.com/en-us/library/aa337556.aspx). 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.