Reload a table quickly.

4

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 (
	Id INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_DestinationTable PRIMARY KEY,
	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;
GO
CREATE TRIGGER dbo.trDestinationTable
    ON [dbo].[DestinationTable]
    FOR DELETE, INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON
    END;
GO

INSERT INTO dbo.DestinationTable VALUES ('123','Yes'),
		('789','Yes'),('345','Yes'),('901','No');
GO

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

CREATE TABLE dbo.LoadTable (
	Id INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_LoadTable PRIMARY KEY,
	Col1 varchar(50),
	Col2 varchar(50) CONSTRAINT ck_LoadTable CHECK (Col2 IN ('Yes','No'))
	);
GO
INSERT INTO dbo.LoadTable VALUES ('abc','No'),
		('ghi','No'),('mno','Yes'),('stu','No');
GO
CREATE INDEX ix_LoadTable ON dbo.LoadTable(Col1);
GO

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;
GO
-- 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;
GO
-- After
SELECT * FROM dbo.DestinationTable;
GO

reloadtable

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).

4 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,
    Thomas

    • 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)
    Greetings

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,628 other followers

Follow me on Twitter

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