Can we add a column in a specific position in sql server???

Question

Hi, I want to ask that, can we add one or more columns at a specific position in existing table by sql query(not graphically). Thanks: Anurag Singh

Vikrant More · Answer

In SQL Server it allow to add column at particular position using SQL Server Management Studio, right click on table and then design select the row where you want to add column right click Insert Column provide column name and data type you want then save it. If you are getting any error saving it; then you need to uncheck the column for "prevent saving changes that required table re-creation" setting,for this select Tools -> Options -> Designers -> uncheck prevent saving changes that required table re-creation "by default its checked".

Anurag Singh · Answer

Ok. Thanks Vikrant.

Jebin Dhanush · Answer

You can take the table script and add the extra column where you want ,then write insert script from old table to New table and drop old table.

Samir Bhogayta · Answer

Hello Friend, It's not possible into sql server due it's relational database system. It's possible into mysql only.

satish patro · Answer

while inserting column through design it says " prevent saving changes that required table re-creation " as per @vikrant, But, will it backup data or affter that I will only get proper schema without any data?

Vikrant More · Answer

i raised same question 2 year ago please refer link below, http://stackoverflow.com/questions/12671629/how-to-add-a-column-at-a-specific-position-in-a-table if you want to change column position any how; you can create view on it by specifying proper position of columns.

Vulpes · Answer

It can't be done in code unless you do essentially what SSMS is doing behind the scenes which is not a good idea IMO. Does it really matter anyway what order the columns are actually in? What does matter is that when you query the table you can return the columns in whatever order you like.

Anurag Singh · Answer

I have also read this. So, it may be correct answer.

Vikrant More · Answer

As per my study it wont possible in SQL Server using query.

Anurag Singh · Answer

Hello Vikrant, I don't want to do it graphically. I am looking the answer of this question by query.

Anurag Singh · Answer

This is for mySQL, but I am talking about SQL.

Akhil Garg · Answer

http://blog.sqlauthority.com/2013/03/11/sql-server-how-to-add-column-at-specific-location-in-table/