SQL SERVER – Change Order of Column In Database Tables

One question I received quite often. How to change the order of the column in database table? It happens many times table with few columns is already created. After a while there is need to add new column to the previously existing table. Sometime it makes sense to add new column in middle of columns at specific places. There is no direct way to do this in SQL Server currently. Many users want to know if there is any workaround or solution to this situation.

First of all, If there is any application which depends on the order of column it is really not good programming and will create problem for sure in future. In ideal scenario there should be no need of order of column in database table. Any column can be any where and it can be used for any data process (INSERT, UPDATE, SELECT).

There are few cases where order of column matters. Let us see the valid and invalid cases of dependence on order of column.

Solarwinds

If there is application of BULK INSERT or BCP the order of column matters when creating the table. This is valid reason for the case where order of column matters. If there is insert statement where column names are not specified the order of column will create issue, this case demonstrate lazy developer and inappropriate coding style.

If you really want to insert your column at any specific place. There are two different ways to do that.

Method 1 : Add column in Management Studio using GUI and visual aid and create the table with necessary order of column. If table is too large, this put lock on entire table and create temporary outage for that table to be used.

Method 2 : Create new table with the name “New_YourTable” name with your desired table structure and column order. Insert values from your existing “YourTable” to this “New_YourTable”. Drop the existing table “YourTable” and rename “New_YourTable” to “YourTable”. This is again resource consuming exercise along with chance of getting something wrong if this is heavily used production server.

Method 3 : Just do not worry, keep the column as they are but you can create view on the base table with your desired column order.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf)
Next Post
SQL SERVER – 2005 – Connection Strings For .NET

Related Posts

No results found

40 Comments. Leave new

  • Hello,

    I with agree Vinod, you can move around columns in design view. so, simple select column in design view and just move where to want to.

    Done and Dusted. :)

    thanks.

    Reply
  • Great article. I was wondering if you had any insight into why the following query returns different column order if I uncomment the 2nd column:

    SELECT clmns.name AS Name
    –, AT.assembly_qualified_name AS AssemblyQualifiedName
    FROM sys.columns AS clmns LEFT OUTER JOIN
    sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN
    sys.assembly_types AS AT ON AT.name = usrt.name AND AT.schema_id = usrt.schema_id
    WHERE (clmns.object_id =
    (SELECT object_id
    FROM sys.objects AS o
    WHERE (name = ‘SOME__VIEW’) AND (SCHEMA_NAME(schema_id) = ‘dbo’)))

    BizTalk uses WCF-SQL adapter to connect to view and creates an XSD. When I deploy my application from Dev to Staging tier, it complains about the column sequence. When I ran the above query, the problem became visible as it showed the different sequence in column order in the view response.

    Thanks much!

    Reply
  • Chanchal Sheik
    March 25, 2013 9:40 pm

    SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName

    **this will create table New Table with columns in the above sequence.

    Reply
  • We can change the order by R.C on Table > Design
    Set the Sequence of Column that we need in to order.
    ( you make settings that “cesar” suggested on September 17, 2011 at 2:37 am)
    Thank you cesar and Pinalbhai…

    Reply
  • I want to insert my column in between two column having any query to do this in sql management studio 2005

    Reply
  • i want to add a new column with the specific location(After the column) in the mssql server 2012 not go to the design step pls give some suggestion

    Reply
  • hi,

    can anybody tell me how to solve the below query

    example:- i have a column with below numbers
    1
    2
    3
    7
    8
    9
    12
    13
    14
    15

    i want output like
    1-3,7-8,12-15

    Please can anyone tell me the solution

    Reply
  • Hi Pinal,

    I have a question. I have a table, with half million records, now I decide to change the order of the column. Does it in any way affect query performance on this table.

    Simple query as “select * from table” before and after changing the order of column?

    Reply

Leave a Reply

Menu