Renaming Tables

Question

Renaming Tables

bridgt01

SSCommitted

Points: 1890
More actions
April 19, 2010 at 2:24 pm

#144120

We have a database that maintains book information. We have a job that executes twice daily against this database to prime a staging_table to have updated information. When that step is complete this staging_table is renamed to staging_table2. After this completes then the main_table is renamed to staging_table. The problem occurs when on the final step we rename staging_table2 to main_table. (EXEC sp_rename 'staging_table2', 'main_table') A lot of blocking occurs as one can imagine during this step as users pound this table while this renaming is occurring. Is there a better way to perform this type of update to make things smoother? This is SQL Server 2000 Enterprise Edition

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 1

Using sp_Rename takes less than 65 milliseconds to execute even on the slowest of boxes and it's usually a whole lot faster than that. Are you sure that it's the rename that's causing the problem and not something else in the proc that does the work?

As a sidebar, instead of renaming the table, you might try using a view as if it were a "synonym". You'd use ALTER VIEW to change it's source back and forth between the two tables. ALTER VIEW is even faster than sp_Rename and shouldn't cause as much blocking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bridgt01 SSCommitted Points: 1890 More actions · Answer 2

I'm fairly certain that it's the renaming of the tables that is causing the problems. The step to rename main table to staging took over 2 hours this morning. Your idea of using a view is a good one. I will take a look into that. There could also be a disk bottleneck. I'm new to this environment and am just now getting a look at things.

Ken McKelvey SSCoach Points: 18874 More actions · Answer 3

The problem with either renaming the tables or using ALTER VIEW is likely to be obtaining the schema lock.

To avoid this, you should update the main table from the staging table.