0

I wants to build the tool to reorder the fields of already exist table with data in SQL Sever 2008 R2. Also I want to create a logic to add a column in any specific position. In Mysql or firebird they have the options and TSQL queries to do that.Any body please share whether we have that options in SQL Sever 2008 R2. If any smart logic available means share it to me. Thanks in advance.

KESAVAN PURUSOTHAMAN
  • 453
  • 1
  • 5
  • 12
  • 4
    The only way is to rebuild the table by creating a new one and importing all data into it (as SSMS does behind the scenes). There is no syntax in SQL Server to alter or manually specify the ordinal position of columns. – Martin Smith Mar 06 '13 at 12:15
  • thanks Mr. @MartinSmith .now i my working on that way only which you said.just i clarify that where is any other way to do it. – KESAVAN PURUSOTHAMAN Mar 06 '13 at 13:35
  • If you think this question as favorite in SQL server 2008 R2 .Please mark it in above. – KESAVAN PURUSOTHAMAN Mar 06 '13 at 13:37
  • possible duplicate of [SQL Server 2008 : Cannot Insert new column in the middle position and change data type](http://stackoverflow.com/questions/6121884/sql-server-2008-cannot-insert-new-column-in-the-middle-position-and-change-dat) – Pondlife Mar 06 '13 at 15:17
  • possible duplicate of [SQL Server 2008 R2 Add column into a specific location](http://stackoverflow.com/questions/4999461/sql-server-2008-r2-add-column-into-a-specific-location) – Metro Smurf May 23 '14 at 14:58

1 Answers1

2

Re-ordering the columns of an existing table is going to be a costly process. The table will have to be rebuilt from scratch. SQL does this for you by creating a copy of the table using the new order and then inserting the existing data into that table. Once this is done, the initial table is dropped and the new one put back in place.

A better solution would be to use a VIEW. They can be changed at will.

PseudoToad
  • 1,504
  • 1
  • 16
  • 34
  • Thanks Mr @Gizmo.Drop and create a new table may lead the dead pace creation on db i think. Is there any other smart way to do it. – KESAVAN PURUSOTHAMAN Mar 07 '13 at 08:36
  • 1
    I would recommend that you not drop and recreate tables unless absolutely necessary. If you use a view, you can use an ALTER VIEW statement to reorder the columns without impact to performance. Keep in mind that reordering of columns can have other impacts if your dev staff uses orderinal field locations (e.g. calling COLUMN 4 instead of LAST_NAME). If your dev staff does this, COLUMN 4 may or may not always be LAST_NAME. – PseudoToad Mar 07 '13 at 15:02