Renaming a field with constraint

Question

Renaming a field with constraint

Bernard Omiple

Old Hand

Points: 308
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: 246960 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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bernard Omiple Old Hand Points: 308 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bernard Omiple Old Hand Points: 308 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 Old Hand Points: 308 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.