Sunday, 4 February 2018

Rearrange Column of Database Table in SQL Server

Many developers come up with a common question of whether a workaround can be made on changing the column structure. The straight answer to them is a “No” for an immediate fix. However, there are methods to rearrange a newly inserted column for an already existing table.

Probably in big organizations, there would be a requirement to create a new column for an already existing table. This new column could be as per the requirement linked to the applications created by the organization. In general terms, a new column added for an application in the middle of the table obviously makes no sense and is considered unethical for table modification. However, in case the situation is too worse to just proceed and implement a new column to the existing table, then here are some workaround.

Method 1: Drop and Recreate Table If No Data Available in the Table

Chances are high that a table would have been created and flushed with data and truncated. In such cases, if you find no data in the table where you need to add a new column then just drop the table and recreate with the new column added in the table structure.

Warning: Please do not drop the table in case you find data loaded in it. 

Method 2: Create a View for the Table

The simplest form is to create a view for the base table in whichever order you desire.

Method 3: Use GUI by Using Design

1. Expand the database where the table is located.
2. Right-click on “Tables” and select “Filter” to select the desired table.
3. Right-click on your table and click “Design”.
4. Here, you will be able to reorder the table structure based on your requirement.

Method 4: Create a New Table and Rename the Table by Dropping Old One

Create a new table say with “_1” with whichever table order of your choice. Once the table is created, import the entire data present in your table to a newly created “_1” table. After transferring the data to a new table, Rename the table with “exec sp_rename 'schema.old_table_name', 'new_table_name' “ command. This can produce a warning message, but nothing to worry about.

Note: Please do not use this method for critical tables.

0 comments: