sql server - Stored procedure - truncate table

Question

I've created a stored procedure to add data to a table. In mock fashion the steps are:

• truncate original table
• Select data into the original table

The query that selects data into the original table is quite long (it can take almost a minute to complete), which means that the table is then empty of data for over a minute.

To fix this empty table I changed the stored procedure to:

• select data into #temp table
• truncate Original table
• insert * from #temp into Original

While the stored procedure was running, I did a select * on the original table and it was empty (refreshing, it stayed empty until the stored procedure completed).

Does the truncate happen at the beginning of the procedure no matter where it actually is in the code? If so is there something else I can do to control when the data is deleted?

Answer 1

A very interesting method to move data into a table very quickly is to use partition switching.

Create two staging tables, myStaging1 and myStaging2, with the new data in myStaging2. They must be in the same DB and the same filegroup (so not temp tables or table variables), with the EXACT same columns, PKs, FKs and indexes.

Then run this:

SET XACT_ABORT, NOCOUNT ON;      -- force immediate rollback if session is killed

BEGIN TRAN;

ALTER TABLE myTargetTable SWITCH TO myStaging1
WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ));
-- not strictly necessary to use WAIT_AT_LOW_PRIORITY but better for blocking
-- use SELF instead of BLOCKERS to kill your own session

ALTER TABLE myStaging2 SWITCH TO myTargetTable
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));
-- force blockers off immediately

COMMIT TRAN;

TRUNCATE TABLE myStaging1;

This is extremely fast, as it's just a metadata change.

You will ask: partitions are only supported on Enterprise Edition (or Developer), how does that help?

Switching non-partitioned tables between each other is still allowed even in Standard or Express Editions.

See this article by Kendra Little for further info on this technique.