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 (https://blog.sqlauthority.com)
42 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.
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!
SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName
**this will create table New Table with columns in the above sequence.
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…
I want to insert my column in between two column having any query to do this in sql management studio 2005
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
K.Raman – I have given three methods in the blog.
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
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?
Problem is ,
we have two tables , one is live data , other is archive data.
so when we archive data we use the insert command on the base of live table
insert into archive_table
select * from livetable where …condition
so in this case ORDINAL_POSITION of every column in both table should be same.
Method 1 is actually implemented behind scene as method 2, correct me if I am wrong. When you generate the script before saving in method 1, you will see it’s implementation.