Inserting a column in SQL Server at a position in a table

Hello I need to add a column to a table in SQL Server 2008. If I use the script below:

ALTER TABLE SampleTable ADD SampleColumn bigint NULL

it adds the column at the end of the table. What if I want the column at a position in the table using script only. For some reasons which are hard to explain, I cannot drop table and execute a new create table script.

An开发者_StackOverflowy ideas and suggestions!

Column order is irrelevant.

Observe:

SELECT Col1, Col2, Col3
FROM Table

SELECT Col3, Col2, Col1
FROM Table

Column order only matters if you use SELECT * which is another reason not to do that.

Besides recreating the table, there is no way to change the default column order in the metadata.

If you need a workaround, it's possible to create a VIEW that selects the fields in the desired order and use that.

If you can use SSMS, then it's easy -- just "design" your table (r-click the table), add your column, then drag it up/down in the list wherever you want it.

That said -- if you view the change script for doing this (i.e., all of the TSQL behind that simple drag & drop), there is an awful lot that actually goes on automagically (including re-creating of the table).

I know column order DOES matter (sorry @JNK) for someone who likes to follow a standard organizational method (like having all of your foriegn keys toward the top, etc.), or someone supporting queries/procedures that don't specify columns (like you can do with insert statements, or selects based on position).

There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table. That said, column order shouldn't matter - are you sure that the column needs to be inserted in order?

Likely your best bet is to just use the GUI, script it out, and then change the constraint name to something reasonable within the script. You're right that the numerical constraint name isn't ideal, and it's not a best practice to allow SQL Server to determine your object names.

check the below link for more: stackoverflow.com/questions/965927/inserting-column-between-other-columns-in-sql-server-using-script