Reload a table quickly.
6November 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
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).
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.
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
*nod* Yea, that’s a variation on the rename swap. It’s great, but at the very least you will lose any object level security. This way you don’t.
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.