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
Thanks.
alter-tabledatabase-designddlsql serversql-server-2008-r2
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
Thanks.
Best Answer
You shouldn't care enough about the physical column ordering of an existing table that you would want to invite the risks involved with changing it. The only case where this might be a concern is if you're using
SELECT *
and want that output arranged in a certain way - and you shouldn't be doing that, you should be listing the column names explicitly (and you can then put the columns in any order you want, or create a view that does that).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
a
aboveb
because I'm OCD and wanted the columns in alphabetical order: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?