69

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
Improve this question
4
  • 8
    There's no real way to change the column order - Although SSMS will pretend there is, it actually creates a new table, copies the data over, deletes the original and renames the new one. You should really just learn to live with the fact that the columns may be in any order, and that there's no inherent meaning to the order. You'll be more productive in the long run. Commented Apr 3, 2013 at 8:00
  • 27
    Though many people are pointing out the column order should not matter I think they are missing the greater picture. We as developers may not be the only folks accessing the database. Analysts and the like often need to view the data and column order can make a big difference to them. Column order also makes it easier to view hierarchical data. Commented Jun 10, 2014 at 22:21
  • 2
    Agreed webworm. but further than that, some software doesn't follow best practice. I have an application that uses a database that is merge replicated. the application will ignore the rowguid column added to a replicated table as long as it is the last field in the table. Columns added after the rowguid column don't behave correctly in the application. Bad programming? of course. But I didn't write it and I have to live with it until a fix is available. Commented Jul 30, 2015 at 20:46
  • 1
    these people "pointing out the column order should not matter" do not understand/thinking about the internals of SQL Server and how it works behind the scene. I have a lecture about SQL Server internals: Tables structure where in one of the demo I create two databases > execute the same script which start with creating a table > Result is that one DB is twice the size of the other DB, and I executed the exact same script except of changing the order of one column in the table :-). By the way, Next time I will present this lecture online in English will be on March 13 19:00 UTC. Commented Feb 3, 2019 at 7:13

7 Answers 7

Reset to default
128

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

enter image description here

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Just want to mention that SQL Server script generated will create the temporary table(s), make the INSERT INTO and drop the temporary tables. That what you expect and don't really want to script manually.
You may also need to increase the timeout, if the table has data.
@Aman Will this cause to loose data which is already in the table?
@f470071 No and as I worked using this i did not loose any data. Kindly try on test table and then use. But The Best Practice After you change this option is to check the upper option Auto generate change scripts in order to prevent data lose
16

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.

Improve this answer

1 Comment

Hi You may use statement insert into<table_name1> select * from <table_name2> to control that fields list is the same for both tables. In this case order the columns are important.
9

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

Improve this answer

Comments

4

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.
Improve this answer

Comments

3

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...

Improve this answer

3 Comments

Row order may not matter when querying the database, but it affects how the columns are displayed in the SMS GUI, which is the only reason I'm here.
@DCShannon As an update - and if ordering the columns is of biggest concern, then you could expose the table via a view, use SELECT Member_ID, MemberName, MemberAddress from TableName to define your view. Wherever you reference your table you can simply reference your view. Then your columns will be ordered the same every time as the view specifies that order explicitly.
The reality is that having to manually specify your SELECT fields every time you view data in SSMS is a cost to company! The point here is to improve the efficiency of your data investigations, and that is a GOOD idea. Well-ordered columns make the data easier to read and digest quickly when you're sniffing around, which happens on most days in a development house with data-intensive projects.
1

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

Improve this answer

Comments

-4

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'
Improve this answer

1 Comment

None. It's for MySQL.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.