Insert new field in the middle of a table

Question

Insert new field in the middle of a table

thriveni

Ten Centuries

Points: 1104
More actions
January 13, 2004 at 1:39 pm

#107357

I would like to insert a field in the middle of a table in SQL server 2000. What I mean is that I have (for eg. ) 25 fields in the table and I want the new field to be the third field. Is this possible at all ?
I have deleted all the data from this table. I would prefer not to drop the table and re-enter all the fields again.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

While there is no real reason for that what you want, you can achieve this by using Enterprise Manager. Open the table in design view, right click on the column in question and choose 'Insert new...'. As you don't have any data in your table, saving won't take too long.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

thriveni Ten Centuries Points: 1104 More actions · Answer 2

thriveni

Ten Centuries

Points: 1104

January 13, 2004 at 1:47 pm

#489853

thanku. that works

johncyriac SSCommitted Points: 1542 More actions · Answer 3

It will work ,but EM drops and recrates the table and related objects ,carefull while dealing with production data

regards

john

David.Poole SSC Guru Points: 75913 More actions · Answer 4

Unless you have a very good reason for this I would always insert the field at the end of row.

After you have done it, remember to do an sp_recompile on the table and sp_refreshview on any views that use the table, otherwise you may get some strange results from your apps.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Because the original poster wrote he has deleted all data, I assumed that he is not on a production box, but rather in a designing stage.

That's what I meant, that there is no real reason for placing a column at a certain position.

However, it is prudent to first place fixed-length columns before variable length ones. Within the variable length the non nullables before the nullables. But the only reason for this is related to the storage engine. As for the relation engine it's all the same.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 6

I have used EM to do this regularly, and even on Prod boxes. But definitely OFF hours.

Since there is no data in the table an alternative also would be to script off the table definition via EM, add the field into the script and recreate the table. This way you don't have to renter all of the fields.

Frank, I don't understand why it is "Prudent" to put fixed length first, then the variable length fields. My understanding is the underlying data is physically stored this way no matter how you logically order the field.

KlK

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Take a look at Antares explanation here

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=17899

No big deal at all, but worth noticing

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 75913 More actions · Answer 8

I think Antares answer could do with being inserted in the FAQ section.

LinkedIn Profile