SQL Server Performance Forum – Threads Archive

Add Columns in the Middle of the TableThru Scripts

Hai Folks
I want to add a columns in the table in between not thru entriprise manager, but thru scripts. Is it possible. Thanks in advance. Soma

Why not, refer to books online for ALTER TABLE…ALTER COLUMN topic for more information and code example. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Thanks Satya,
i have checked but in the books online it was specified to add in the last, i want to add in between two fields thru scripts but not thru enterprise manager, it will be helpful if you give with an example.

Frankly, I haven’t tried that way. But you can do the following :
Start PROFILER
And use EM to change the design for a table.
Check profiler trace for the events and statements used to do so. HTH (If not other peer might help you to achieve the task) _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I think the order of the columns is stored in the system tables, I *think* its colorder field in syscolumns. There is also a colid field which may be to do with defining the order of the columns as stored on disk per row. Dont take any of this as gospel. You need to do thorough testing if you intend to modify the system tables.

Hi rsoma, I believe there’s no T-SQL command that will achieve this task. You may want to take a look at system tables syscolumns and sysobjects. But I myself would not update system tables [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Just curious…why do you want to insert in between? I believe column order is not relevant for the storage of the data. If you want to present data on a client, you could write your query with the columns in the order you wish. A workaround that I can think of is to re-create the table structure, insert your new column in between then copy or append your data to the new structure. Jon M

I suspech changing the colid in the system tables will give you unexpected results. My understanding is that when the data is written to the data page, the column id is not written and so the columns are written in the order they were created (except for the case where we have a variable lenght column). Any new columns must therefore be added to the end. So agreeing with rest of the team, I would say recreate the table if you have to do something like this. I agree with Jon. Why do you need to insert coulmns in between? You can always modify the column order while querying. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I disagree and discourage modifying SYSTEM Tables for fancy purposes, as referred while using SELECT… you can list the preferred way to represent the data. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I ran a trace and changed columns order using EM. What it does is: – create another table temp_mytable with the new column order.
– insert into the new table rows from old table if there are any.
– drop mytable
– rename the table temp_mytable to mytable. Bambola.

Thanks for the steps Bambola. This confirmed what the team was saying about this topic. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>