CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
RedDogsRock
RedDogsRock

Reputation: 17

Stored procedure - truncate table

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

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:

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?

Upvotes: 1

Views: 2555

Answers (2)

Charlieface
Charlieface

Reputation: 71233

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.

Upvotes: 2

RedDogsRock
RedDogsRock

Reputation: 17

The sp is being called by code in an HTTP Get, so I didn't want the table to be empty for over a minute during refresh. When I asked the question I was using a select * from the table to test, but just now I tested by hitting the endpoint in postman and I never received an empty response. So it appears that putting the truncate later in the sp did work.

Upvotes: 0

Related Questions