John1.5.nlt (2/4/2016)
So is this the best way to manage this with minimal downtime? I mean this in relation to creating a transaction, truncating and loading the data and then closing the transaction. The issue at hand is not a lack of availability, I don't care if they have to wait, I am more concerned about them trying to access something that is not there and getting an error.
By doing truncate/load in transaction instead of rename you will get the same behaviour. The downside of this, as you alluded to, is the table will not be available during the load. the rename takes a schema mod lock and since done in a transaction the transaction has to commit before selects can continue. even if you add the nolock query hint to the select, a shared schema lock is still taken and thus has to wait for the schema mod lock to be released.
Here is a quick test you can do to verify that queries will not try to select from a non-existent table.
In one query window create tables and run the rename transaction, I added a wait in between the renames for the purpose of the test.
CREATE TABLE tmp(id int);
CREATE TABLE tmp_new(id int);
BEGIN TRAN;
EXEC sp_rename 'tmp','tmp_old'
WAITFOR DELAY '00:01:00';
EXEC [sp_rename] 'tmp_new', 'tmp';
COMMIT;
While this query is running, in a second query window check if there is any table named tmp in the database. You should only see tmp_old and tmp_new.
SELECT *
FROM sys.objects
WHERE name LIKE 'tmp%'
If there is not a table named tmp, then try to select from it(use NOLOCK if you want)
SELECT * FROM tmp WITH (NOLOCK)
This select will wait until the rename transaction is committed. when done dont forget to drop the tables.
DROP TABLE tmp;
DROP TABLE tmp_old;