Michael J. Swart

Takeaway: Stick with sp_rename and leave the ALTER TABLE SWITCH statement for partitions.

Earlier in the month I was at a talk given by Don Vilen called To Partition or Not To Partition The questions .. and the answers. In the talk he gave an interesting tip about switching partitions. (Remember that switching partitions is a method that is used to implement the sliding window strategy. For more info, search for the keyword SWITCH in this page).

Here’s the tip: Notice that the partition arguments in the ALTER TABLE – SWITCH command are optional. This means that you can swap the data of any table with another. It doesn’t have to be partitioned. But that staging table must still be empty.

So as I thought about how to get around the empty staging table restriction, I had an idea (Great Scott!) This is the diagram I made in my notes:

Not a flux capacitor

Moments later, Don Vilen summed up this idea: “The staging table must be empty. But there’s nothing stopping you from taking a third table with data and switching that into the newly emptied table.” I’m paraphrasing. He said it much better than that. Maybe an example would help:

use tempdb;
 
create table primary_table (i int primary key);
create table staging_table_with_data (i int primary key);
create table empty_staging_table (i int primary key);
insert primary_table values (1), (2), (3)
insert staging_table_with_data values (4), (5), (6)
 
-- the following does not work:
alter table primary_table switch to staging_table_with_data;
/* this fails with:
    ALTER TABLE SWITCH statement failed. The target table
    'tempdb.dbo.staging_table_with_data' must be empty.
*/
 
-- but this does work:
alter table primary_table switch to empty_staging_table;
alter table staging_table_with_data switch to primary_table;

When would you use this?

The original reason that the SWITCH statement was implemented was to help transfer data efficiently by using partition switching. But I think we can use this feature on whole non-partitioned tables in order to quickly transform data.

A great example is an update statement that applies to a large number of rows that has to be done quickly. Myself, I’ve come across a situation where we were converting local times to UTC times and needed speed.

Is this any better than sp_rename?

In the domain of non-partitioned tables, the answer is no, not at all. In fact the code above can probably be written like this:

use tempdb;
 
create table primary_table (i int primary key);
create table staging_table (i int primary key);
 
insert primary_table values (1), (2), (3)
insert staging_table values (4), (5), (6)
 
exec sp_rename @objname='primary_table', @newname = 'old_data', @objtype = 'OBJECT'
exec sp_rename @objname='staging_table', @newname = 'primary_table', @objtype = 'OBJECT'

You probably have seen this kind of technique used by tools that generate scripts to change table definitions. In fact this is the only technique you can use to add or remove an identity column to a table.

So then what’s the difference between them? Why would you choose one method over the other. Hopefully you can use the rest of the article to make an informed decision.

Restrictions

When renaming tables using sp_rename, there are very few restrictions, in fact I’m pretty sure there are only four:

• Neither the primary or staging table can be temporary
• The table can’t be published (via replication)
• The new name can’t clash with an existing object name.
• Enforced dependencies – like schema-bound views – can’t exist.

In fact, aside from syntax errors, I’ve never ever seen sp_rename fail. However, when swapping non-partitioned tables using the SWITCH keyword there are more pitfalls to watch out for:

• The columns, types, indexes and constraints of the primary and staging tables must match.
• too many others to list

To see what could go wrong, read the manual here or check out the error list with:

--roughly 50 rows here:
select text from sys.messages
where text like '%ALTER TABLE SWITCH statement%'
and text not like '%partition%'

But the the best approach would be to test specific scenarios on a test machine. Just to find out what’s possible.

Benefits of one over the other

So if you’re still with me (and congratulations and thank you for bearing with me) you might get the distinct impression that sp_rename is the better choice. You’re probably right. But I’ll still try to sum up the benefits and drawbacks of each method here:

sp_rename:

• Almost never fails.
• Different table definitions are possible.
• Indexed views (which rely on schema-bound views) are not allowed.
• Foreign keys and indexes must be recreated before or after the rename.

SWITCH

• You don’t have to recreate indexed views. But the staging table must have matching views before the switch.
• Other foreign keys, indexes and constraints do not need to be redefined. But as before, the staging table must be prepared properly.
• Minimal or zero downtime.
• The big drawback is the rule that requires the staging and the primary tables to have matching definitions.

So, like I said in the beginning: For the most part, stick with sp_rename and leave the ALTER TABLE SWITCH statement for partitions.