Fast, Atomic Table Replacement

Question

I have a rather simple command which I occasionally run:

BEGIN TRAN T1;
truncate table mytable
insert into mytable select name from myview
COMMIT TRAN T1;

This command has two ugly side effects: Firstly, select requests on mytable often time out. Secondly, select requests on mytable sometimes return no results. I don't care if it returns the pre-transaction results or the post-transaction results, but don't want it to return anything in the middle, or to time out. One solution I thought of, and which will almost definitely help, is to first copy the view into a temp table (as the view is a little expensive). This won't fully solve the problem, but it will almost definitely make the window narrow enough for the problem to be ignored. Frankly, the window is narrow enough to ignore it now, but I don't like ignoring it. Another solution, which is an example of crazy over-engineering, would be to replace the table with two tables (e.g. a double-buffer), and call the newest, properly populated table.

Is there a more elegant way to replace a table with a new one?

Answer 1

Nobody should see anything in the middle. If they do, it means you're doing dirty reads and you deserve every bad result yout get.

You can use ALTER TABLE ... SWITCH PARTITION ... to 'switch' in the content of another table (must have identical structure and constraints). The operation is as atomic and fast as it gets, it simple changes some metadata pointers around so the content 'magically' moves into the destination table. See Transferring Data Efficiently by Using Partition Switching. Neither the source nor the destination do not need to be explicitly 'partitoned' tables, the switch operation works on normal, single partitioned, tables too,.

Answer 2

There is an ALTER TABLE SWITCH technique, or you can use sp_rename - all discussed in detail here.