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

Question

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

User · Answer

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

User · Answer

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

User · Answer

I tried this and dont see any way of doing it   here is my approach for it     Right click on table and Script table for Create and have this on one of the SQL Query window  EXEC sp rename  Employee    Employee1   -- Original table name is Employee Execute the Employee create script  make sure you arrange the columns in the way you need  INSERT INTO TABLE2 SELECT   FROM TABLE1  -- Insert into Employee select Name  Company from  Employee1 DROP table Employee1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server