how to add the new column in specific location in a table in sql server

The table is a set of sets in relational model. As there is no explicit order between the rows, there is no explicit order between the columns either. But you can reorder them during projection by specifying the columns you need instead of using *.

But of course the RDBMS, SQL server in your case is still using some ordering. You can't really add the field at specific position, but you can change field order. Let me quote the answer from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58912[^]

Quote:

The Information_Schema.Columns table has an ORDINAL_POSITION field. Is it possible to simply add the desired columns via ALTER TABLE then change the ORDINAL_POSITION for the table in Information_Schema.Columns?

I haven't tried this, but it seems the following approach may work. This example assumes there is a table with 2 columns. Basically add the new column, assign it to position 1, then assign the old column to position 2.

-- Add the column
IF NOT EXISTS (SELECT * FROM [information_schema].[columns] WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn')
BEGIN
ALTER TABLE [dbo].[TargetTable]
ADD TheNewColumn int
END

-- Move the new column's ordinal position to the top.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 1
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn'

-- Move the old column's ordinal position to the bottom.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 2
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheOldColumn'

Some SQL guru is probably screaming right now that this would screw up something at a system level. That's why I'm posting it here.

Altrough MSDN[^] says, that this can't be done from T-SQL, but from SQL Management Studio. By the way, wha't wrong with that option?