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.

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 (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – Change Order of Column In Database Tables

  1. Thanks for the post. I thought it was a good question. I personally like to have a rhythm and reason to the order of columns, especially when printing out a diagram of the database. I wish there was a better way of doing it instead of locking the table or creating a new table and deleting the old. Thanks for the posts.

  2. One shouldn’t really change the order too often

    I remember when we had to swap Latitude & Longitude columns, it was a mess, so we just ended up renaming the column names.
    It’s weird to see column1, Longitude, Latitude, column2 order though

    SSMS sucks because it creates a TEMP table
    copy everything from old table to TEMP
    then rename TEMP table to your old table name
    It ALWAYS times out if there is a lot of data

    Do it in SQL Scripts

  3. Pingback: SQL SERVER – Order of Columns in Update Statement Does not Matter Journey to SQL Authority with Pinal Dave

  4. Regarding method 2:
    I would suggest instead of dropping and recreating the original table – after copying the data to the temp table, simply truncate the original table, add the new column, and then move the data back into it. Advantages: You won’t have to worry about recreating keys/constraints/indexes on the table. Also once the table is truncated, adding a new column shouldn’t cause a lock.
    Disadvantages: Pinal’s method involves moving the data only once, and my alternative moves it twice.

  5. Good article.

    I would be wary of using your method 2. This is because it would be easy to forget to add all the appropriate permissions to your new table, or possibly triggers, constraints, etc. I prefer the other options, where the original table is retained.

    With regard to method 1, remember that when you use SSMS, instead of executing the changes straight away you can have the changes scripted, so you could create a scheduled job to make the changes out of hours, minimising disruption?

    Or how about recreating all the columns that follow the position where you want to insert one, then systematically copy data from the “old” to “new” columns, and then drop the old column and rename the new one. An example is below, where our table originally has col1 and col3, and we want to insert a col2 in between them.

    CREATE TABLE test (col1 INT, col3 INT)

    INSERT INTO test VALUES (1,3)
    GO 1000000

    ALTER TABLE test ADD col2 INT
    ALTER TABLE test ADD col3_new INT

    UPDATE test SET col3_new = col3

    ALTER TABLE test DROP COLUMN col3
    EXEC sp_rename @objname = ‘test.col3_new’, @newname = ‘col3′, @objtype = ‘COLUMN’

    SELECT * FROM [YFi].[dbo].[test]

    Cumbersome, but in some circumstances it could work. If locking becomes an issue you could replace the UPDATE statements with a cursor, that only updates (and therefore locks) one record at a time? Not pretty, but it could fit this occasional requirement?

  6. if we want to change datatype of a table’s column then we use below statement,

    ALTER table table_name
    alter column column_name new_datatype

    similarly I need to rename the column name then how it is possible?

    (somebody say avoid to use the following qry sp_rename ‘newColumnname’, ‘oldname’, ‘COLUMN’)

    Regards,

    Raja

  7. In SQl server 2008:

    Use SQL Server Management Studio (2008) and from the menu go to Tools -> Options -> Designers -> Table and database designers and Unselect default option “Prevent saving changes that require table re-creation”

    For DBA: Can change field order in “colorder” column in syscolumns table

    • Sarin that worked a treat, I’m sure SQL 2000 might have a similar option. I went for the Tools option, then went to design mode, and dragged the column to the right place! Saved it, and it was accepted!

  8. Thanks Sarin Shah, that worked great for me! I cant change the stru of the tables then i went as you said Tools -> Options -> Designers -> Table and database designers and Unselect default option “Prevent saving changes that require table re-creation” and now i can change the stru of my tables!

    Thanks !

  9. Thank you Pinal Dave, your article is always clear.

    But one cannot defend the idea that the Order of colums is not important. I would like to cite two cases:

    To create a table from a tab separated CSV file in DOS format, one uses:

    CREATE TABLE products
    (
    code VARCHAR(40),
    codeSupplier VARCHAR(100),
    Name VARCHAR(255)
    )
    GO

    BULK INSERT products
    FROM ‘D:\tmp\products.txt’
    WITH
    (
    FIELDTERMINATOR = ‘\t’,
    ROWTERMINATOR = ‘\n’
    )
    GO

    One reasonably adds a column ID, as auto_increment field and as Primary key:
    ALTER TABLE products ADD [ID] [int] IDENTITY(1,1) NOT NULL;
    ALTER TABLE products ADD CONSTRAINT PK_products PRIMARY KEY(ID);

    My ID column is at the end of the table. My table shape is very ugly if you show it even in SQL Server Management Studio, ID should be at the first place.

    Case 2:

    One has a directory table with fields:
    ID, Surname, Firstname, Birthday, Address, City, Country, Notes.

    With the time passing, the developper must add a colum like Deathday, as the life is not eternal, to notify the death of our preferred singer Miachael Kackson for example.

    My modified would be like
    ID, Surname, Firstname, Birthday, Address, City, Country, Notes, Deathday.

    It’s a very ugly table shape as Birthday and Deathday are not at the vicinity. If one edits the data in SQL management studio, the birthday and deathday columns would be better one aside another.

    OK One can modify this in SQL Mangement Studio, but it would be better to do it in a quetry, as one could do in MySQL database.

  10. I think you can change the order of columns through the management studio using following steps( I am using SQL Server 2005)….

    1) Right click table name and select ‘Design’
    2) In the design view click the field which you want to move
    3) Then you can see an arrow/triangle on the left bar against the field
    4) Click on the arrow/traingle and hold the left mouse button and drag the field to the appropriate position.

    Cheers

  11. Hello Sir.. I want to show Department Name Through Joining tables.. Show Only Those Department Name in Which No. of Employee Exists atleast 3..

    I wrote this query But Result is Not Perfect ::
    select d.DName,count(e.EID)as EmpID from tblEmp e join tblDept d on e.DID = d.DID where e.EID >=3 group by d.DID ,d.DName

    How Can I do this ::

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

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

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

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

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s