How To change the column order in sql server 2008 R2?
Eg: My table order is Eid------Ename-----Esalary
Know i want this format like Eid---Salary----Ename
You shouldn't care about physical column order in the table. The only case where this should be a concern is if you're using
You can drop and re-create the table, like others have said, with the columns in your preferred order. But this is going to be very disruptive (blocking, I/O) if your table is large, since all of the other users on the system will have to wait for that to complete. If you want to see exactly what Management Studio does, create this table:
Now, in Object Explorer, right-click the table and choose Design. Select the box to the left of the column name that you want to reorder. Drag the column to another location within the table. I moved
Then I clicked the "Generate Change Script" button, and here is the monstrosity that it created (and note this is a heap with no keys, constraints, triggers etc. - this re-create can get pretty complex).
For fun, let's create something a little more complex:
Now we try the same thing:
Oops. Thankfully, we can still proceed to see what the script will look like:
Yeah, that really looks like something I want to run in production, even if the table is small.
The other alternative is to do this quite manually. Say you have this table:
Theoretically, and leaving out the complications caused by keys, constraints, triggers, and schemabound objects, you could "change" the order of the columns by - for each column to the left of the one you want to move - creating a new column, moving the data, dropping the old column, and renaming the new column. This works because the new column is always added to the "end" of the table. Leaving out error handling and any of the other complications:
This of course has the potential to be as disruptive (or worse) as the Management Studio approach of just swapping out the entire table, and gets really complicated if you want to move more than one column.
Now, do you really need to change the column order?
You cannot change what appears to be the physical column order in SSMS without dropping and re-creating the table.
Why not just change the query to select the columns in a different order?
It's not "EASY" possible. But you can create another one and insert the same data again, the way you want.
The simplest way its to change operations statement order. Like:
Do you want to change the underlying table, or just the resuls?
If it's just the results, then simply re-arrange your select statement.
eg. instead of
You could also setup a view based on that table that selects in the desired order.
And of course if you really need to, you could simply drop and re-create the table. Just make sure to take care of exporting and importing the data if you want to keep it.