[sql-server] How To change the column order of An Existing Table in SQL Server 2008

I have situation where I need to change the order of the columns/adding new columns for existing Table in SQL Server 2008.

Existing column

MemberName
MemberAddress
Member_ID(pk)

and I want this order

Member_ID(pk)
MemberName
MemberAddress

If your table doesn't have any records you can just drop then create your table.

If it has records you can do it using your SQL Server Management Studio.

Just click your table > right click > click Design then you can now arrange the order of the columns by dragging the fields on the order that you want then click save.

Best Regards

I tried this and dont see any way of doing it.

here is my approach for it.

1. Right click on table and Script table for Create and have this on one of the SQL Query window,
2. EXEC sp_rename 'Employee', 'Employee1' -- Original table name is Employee
3. Execute the Employee create script, make sure you arrange the columns in the way you need.
4. INSERT INTO TABLE2 SELECT * FROM TABLE1. -- Insert into Employee select Name, Company from Employee1
5. DROP table Employee1.

It is not possible with ALTER statement. If you wish to have the columns in a specific order, you will have to create a newtable, use INSERT INTO newtable (col-x,col-a,col-b) SELECT col-x,col-a,col-b FROM oldtable to transfer the data from the oldtable to the newtable, delete the oldtable and rename the newtable to the oldtable name.

This is not necessarily recommended because it does not matter which order the columns are in the database table. When you use a SELECT statement, you can name the columns and have them returned to you in the order that you desire.

This can be an issue when using Source Control and automated deployments to a shared development environment. Where I work we have a very large sample DB on our development tier to work with (a subset of our production data).

Recently I did some work to remove one column from a table and then add some extra ones on the end. I then had to undo my column removal so I re-added it on the end which means the table and all references are correct in the environment but the Source Control automated deployment will no longer work because it complains about the table definition changing.

The real problem here is that the table + indexes are ~120GB and the environment only has ~60GB free so I'll need to either:

a) Rename the existing columns which are in the wrong order, add new columns in the right order, update the data then drop the old columns

OR

b) Rename the table, create a new table with the correct order, insert to the new table from the old and delete from the old as I go along

The SSMS/TFS Schema compare option of using a temp table won't work because there isn't enough room on disc to do it.

I'm not trying to say this is the best way to go about things or that column order really matters, just that I have a scenario where it is an issue and I'm sharing the options I've thought of to fix the issue

SQL query to change the id column into first:

ALTER TABLE `student` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

or by using:

ALTER TABLE `student` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT AFTER 'column_name' 

Relying on column order is generally a bad idea in SQL. SQL is based on Relational theory where order is never guaranteed - by design. You should treat all your columns and rows as having no order and then change your queries to provide the correct results:

For Columns:

• Try not to use SELECT *, but instead specify the order of columns in the select list as in: SELECT Member_ID, MemberName, MemberAddress from TableName. This will guarantee order and will ease maintenance if columns get added.

For Rows:

• Row order in your result set is only guaranteed if you specify the ORDER BY clause.
• If no ORDER BY clause is specified the result set may differ as the Query Plan might differ or the database pages might have changed.

Hope this helps...