Why Can't I Reorder My SQL Server Columns

Why can't I reorder my SQL Server columns?

You might be interested in the answers to this question.

As to why you can't reorder columns (aside from the SSMS-specific answers posted), I'm not sure you'll find a canonical answer, other than the SQL standard contains no programmatic syntax for doing so - the only way to redefine a table's schema (including the order of its columns) is to drop the table and recreate it. So any management application that provides column "reordering" will most likely be doing that behind the scenes (e.g. SSMS's Design View, as pointed out in the linked question).

Someone with deeper knowledge of the SQL specification could contradict this if there is an explicit reason for not providing a column-reordering mechanism as part of the standard DDL, but I can only conjecture that, since queries define the order of columns in their resultsets anyway, and DBMSes control physical storage according to their individual implementation, that logical column order is essentially meaningless and doesn't warrant such a mechanism.

Can I logically reorder columns in a table?

You can not do this programatically (in a safe way that is) without creating a new table.

What Enterprise Manager does when you commit a reordering is to create a new table, move the data and then delete the old table and rename the new table to the existing name.

If you want your columns in a particular order/grouping without altering their physical order, you can create a view which can be whatever you desire.

How to change column order in a table using sql query in sql server 2005?

You cannot. The column order is just a "cosmetic" thing we humans care about - to SQL Server, it's almost always absolutely irrelevant.

What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLE command, copying over the data from the old table, and then dropping it.

There is no SQL command to define the column ordering.

Is there any reason to worry about the column order in a table?

Column order had a big performance impact on some of the databases I've tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb:

• Primary key columns first
• Foreign key columns next.
• Frequently searched columns next
• Frequently updated columns later
• Nullable columns last.
• Least used nullable columns after more frequently used nullable columns

An example for difference in performance is an Index lookup. The database engine finds a row based on some conditions in the index, and gets back a row address. Now say you are looking for SomeValue, and it's in this table:

 SomeId int,
 SomeString varchar(100),
 SomeValue int

The engine has to guess where SomeValue starts, because SomeString has an unknown length. However, if you change the order to:

 SomeId int,
 SomeValue int,
 SomeString varchar(100)

Now the engine knows that SomeValue can be found 4 bytes after the start of the row. So column order can have a considerable performance impact.

EDIT: Sql Server 2005 stores fixed-length fields at the start of the row. And each row has a reference to the start of a varchar. This completely negates the effect I've listed above. So for recent databases, column order no longer has any impact.

How To change the column order of An Existing Table in SQL Server 2008

I got the answer for the same ,
Go on SQL Server → Tools → Options → Designers → Table and Database Designers and unselect Prevent saving changes that require table re-creation

2- Open table design view and that scroll your column up and down and save your changes.

How to move columns in a MySQL table?

If empName is a VARCHAR(50) column:

ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;

EDIT

Per the comments, you can also do this:

ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR(50) AFTER department;

Note that the repetition of empName is deliberate. You have to tell MySQL that you want to keep the same column name.

You should be aware that both syntax versions are specific to MySQL. They won't work, for example, in PostgreSQL or many other DBMSs.

Another edit: As pointed out by @Luis Rossi in a comment, you need to completely specify the altered column definition just before the AFTER modifier. The above examples just have VARCHAR(50), but if you need other characteristics (such as NOT NULL or a default value) you need to include those as well. Consult the docs on ALTER TABLE for more info.

---

Related Topics