Reload a table quickly.


November 21, 2016 by Kenneth Fisher

Sometimes loading new data or even changes into a table just isn’t going to work. You need a complete reload. A summary table of data from the previous month, for example. It just doesn’t make sense to do an update. You delete/truncate and you re-load. Unfortunately, this leaves the table unusable for a period of time, and depending on your usage and load time that downtime may not be acceptable.

Now if this table is paritioned you’d use SWITCH and bring in a new partition.

For those that don’t know, when a table is partitioned, you can create a new empty partition, and a new empty table, load the table, make the table exactly match the partition (structure, check constraints, & indexes for example) and you can SWITCH it in. The SWITCH part is a metadata operation and is fast!

But what do you do if the table isn’t partitioned? Well, I was having a conversation with Andy Mallon (b/t) and he reminded me of something.

Technically all tables are partitioned. Go take a look at the system view sys.partitions. In it, you will find an entry for every table partitioned or not. Those that are not partitioned only have a single entry for partition 1. As a result, you can, in fact, SWITCH into an empty table. So for example (of course):

-- Set up the initial table
CREATE TABLE dbo.DestinationTable (
	Col1 varchar(50) CONSTRAINT df_DestinationTable DEFAULT(''),
	Col2 varchar(50) CONSTRAINT ck_DestinationTable CHECK (Col2 IN ('Yes','No'))
CREATE INDEX ix_DestinationTable ON dbo.DestinationTable(Col1);
GRANT SELECT ON dbo.DestinationTable TO Doc;
CREATE TRIGGER dbo.trDestinationTable
    ON [dbo].[DestinationTable]

INSERT INTO dbo.DestinationTable VALUES ('123','Yes'),

In order to do the swap we need another table that’s identical (almost) but with different data.

CREATE TABLE dbo.LoadTable (
	Col1 varchar(50),
	Col2 varchar(50) CONSTRAINT ck_LoadTable CHECK (Col2 IN ('Yes','No'))
INSERT INTO dbo.LoadTable VALUES ('abc','No'),
CREATE INDEX ix_LoadTable ON dbo.LoadTable(Col1);

Notice that I have the same indexes, the same check constraints, and the same table structure. I do not have the same default constraints, triggers, and permissions. (I do not promise this is the extent of the requirements for SWITCHing just part of it.) Also I created my non-clustered indexes (NCIs) after loading the data. In some cases, this can be quite a bit faster, particularly with large amounts of data. If you are going to be doing this on a regular database I recommend testing it both ways.

Now let’s move the data into the DestinationTable.

-- Before
SELECT * FROM dbo.DestinationTable;
-- Clean out the old data
TRUNCATE TABLE dbo.DestinationTable;
-- Normally there is a PARTITION # at the end of the statement
-- In this case, it isn't necessary.  You can include it but it
-- will give you a warning that it's been ignored because the 
-- table isn't actually partitioned.
ALTER TABLE dbo.LoadTable SWITCH TO dbo.DestinationTable;
-- After
SELECT * FROM dbo.DestinationTable;


And again, remember that TRUNCATE and SWITCH are both fast operations. In fact, table size will have a very very limited effect on this part of the process, meaning that the actual down time for the users is negligable. (Subsecond or maybe seconds).

6 thoughts on “Reload a table quickly.

  1. Thomas Kern says:

    Hi Kenneth,

    very interesting article, especially the fact that every table is a partitioned table internally (I wasn’t aware of that). When someone asked me to reload a table, I would do it with sp_rename (load the data into a new table, sp_rename the old table to some other name, sp_rename the new table to the old name). Do you know about advantages / disadvantages comparing your solution to mine?

    Thanks in advance and best regards,

    • This actually started out as a comparison between rename/schema swap and partition swap. The biggest difference is that any object level security isn’t lost. Also the tables don’t have to be quite so exactly the same. Speed wise this might be slightly faster (one command instead of three) but either way it’s so quick it doesn’t really matter.

  2. A nice tip, thanks!

    But also, you can do this:
    – write to a new table (takes time)
    – delete the original table (instant)
    – rename the new table to match original table name (also instant)

  3. Tech Monkey says:

    Any reason i couldn’t do that with a tmp table?

    • I’ve never tried it before but I’m almost certain it wouldn’t work between a temp table and a regular table because they are technically in different databases.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: