Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have just created a change script using SSMS 2008 Express, for renaming two columns in a table. However, the change script contains four renames, using an intermediate temp name. Why is this?

EXECUTE sp_rename N'dbo.PerformanceCategories.WeekToDateFormula', N'Tmp_WeekToDateFormulaActual_4', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.PerformanceCategories.MonthToDateFormula', N'Tmp_MonthToDateFormulaActual_5', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.PerformanceCategories.Tmp_WeekToDateFormulaActual_4', N'WeekToDateFormulaActual', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.PerformanceCategories.Tmp_MonthToDateFormulaActual_5', N'MonthToDateFormulaActual', 'COLUMN' 
share|improve this question
add comment

1 Answer

I don't think there's a good answer to your question, other than the fact that automatic script generating tools sometimes have to sacrifice efficiency to make sure they don't cause issues in strange corner cases.

No inside information here, but perhaps they're accounting for something like the following scenario:

The CEO went to an inspirational seminar, and came away with the crazy idea to re-define how the company is organized, including what each "level" is named:

Company  >>  Section
Division >>  Office
Office   >>  Branch

If you simply generated straight sp_rename scripts as so:

sp_rename 'org.Company',  'Section', 'COLUMN' 
sp_rename 'org.Division', 'Office',  'COLUMN' 
sp_rename 'org.Office',   'Branch',  'COLUMN' 

The second one would fail, because a field named "office" already existed. In this case you could fix that by ordering them so the 3rd rename before the second, but what if your renames were actually:

Address1  >>  Address2
Address2  >>  Address3
Address3  >>  Address1

Then there is no ordering that would allow you to perform the rename without error. You'd have to assign unique temporary names first.

share|improve this answer
    
Ah yes, I see where it can be useful, thanks. –  ProfK Oct 13 '10 at 17:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.