I have a type 2 dimension in Azure SQL Data Warehouse.

Essentially I am creating an interim table with

CREATE TABLE myDimension_temp
FROM myStagingTable;


After the CTAS is complete I do a

RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;

Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?

If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?

  • 1
    The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
    – SQLmojoe
    Dec 20, 2017 at 20:56

2 Answers 2


There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.

Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.


RENAME is not transactional ie begin transaction .. rename .. will throw an error. So any attempt to gain an exclusive lock on a table before renaming it is futile as the transaction acquiring the lock must end (and release the lock) before RENAME can run.

Your best bet is to schedule no access during the swap. If access must happen it must be written allowing for failure during the brief interval where the dim table does not exist.

An alternative is to use whole-table partition switching alter table Source switch to Target. I do not know if this has any better locking characteristics in your context. It has the advantage that all tables exist all the time so queries will not fail. They will return zero rows, however, so that has to be considered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.