Renaming a field with constraint

Question

Renaming a field with constraint

Bernard Omiple

SSC Veteran

Points: 278
More actions
August 17, 2004 at 8:14 pm

#164710

Hi all:
I have a table with existing data, and I want to rename a field. This field has a constraint dependent on it. What would be the most efficient way of renaming the field, especially if there are already hundreds of thousands of records in the table? I am seeing two methods:
1. Drop the constraint, create the new (renamed) field, copy the values of the old field into the new field, drop the old field, and recreate the constraint.
2. Create a temp table which is a copy of table; copy the contents of the table into the temp table; drop the table; create a new table with the field already renamed; copy the contents of the temp table into the new table; create constraints and indexes on the new table; drop the temp table.
The second option came to me because I seemed to have read somewhere that the operation in option 1 makes use of temp tables behind the scenes, but I am just not sure. Any advice/help/comment is highly appreciated.
Thanks.
Bernard

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Hi Bernard - what sort of constraint? Some constraints (eg PK, FK) do not need to be dropped before renaming fields.

Otherwise, can't you just drop the constraint, rename the field and then recreate the constraint to reference the new field name?

Regards

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bernard Omiple SSC Veteran Points: 278 More actions · Answer 2

Hi Phil:

Thanks for the reply. I think in writing option 1 that is what i was trying to achieve.

Currently, I am doing option 1. I could just rewrite it as you suggested and test this out against option 2 to check which method is faster.

Regards,

Bernard

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

Not sure how you are achieving this - Enterprise Manager or Query Analyser? I would expect the sp_rename stored procedure to work the fastest via QA. It was the "copy the values of the old field into the new field" part of the first message that alarmed me. This should not be necessary.

Regards.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bernard Omiple SSC Veteran Points: 278 More actions · Answer 4

Yeah, I realized the copy part was not necessary.

sp_rename interests me. I wonder what DBCC RENAMECOLUMN does behind the scenes. Does it do anything about the data? or system tables only? if it does not deal with the data, then sp_rename should be faster.

Thanks.

Bernard

manoj kumar-160165 Valued Member Points: 62 More actions · Answer 5

From SQL BOL

Rename a column

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

Bernard Omiple SSC Veteran Points: 278 More actions · Answer 6

Thanks to Phil and Manoj for all the help.

I will be using sp_rename; makes life easier; no need to drop constraints. I am looking forward to compressing thousands of lines of code into just as many fields that I need to rename.

I searched BOL before I posted this thread but I guess I searched it the wrong way.