3

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'
ProfK
  • 493
  • 5
  • 9
  • 28

1 Answers1

5

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.

BradC
  • 2,220
  • 4
  • 27
  • 36