SQL Server – Is Renaming Tables for Version Updates a Good Idea?

sql server

We have a slightly heavy table which is semi-frequently (every ~5 minute) being recreated by a procedure. I wondered if there was something that could be done in order to minimize the downtime during the table rebuild, and have thought of two possibilities so far (let's call the table "Device"):

Option A
1) Create the new table in the procedure, name it Device_new
2) Rename old table from Device to Device_old
3) Rename new table from Device_new to Device

Option B
Same as Option A, except do it using Partition Switching.
Caveat: Partition switching is significantly more complex to setup, and has quite strict requirements for usage.

Are these options poor solutions, if yes, how would you solve this use-case? Thank you.

Best Answer

You mentioned that partition switching is more complex but that is not the case when the table is not partitioned. One can use SWITCH to efficiently replace the entire contents of one non-partitioned table with another as a meta-data only operation. The requirement for SWITCH is that the source and target table must have the same schema, indexes, constraints, and be storage aligned. These requirements are presumably the same as the rename technique so the effort is similar.

Note that unlike table partitioning with a partition function and scheme, SWITCH of a non-partitioned table can be used in any SQL Server edition.

Below is a sample DDL script for option B:

CREATE TABLE dbo.Device(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_DeviceName ON dbo.Device(DeviceName) ON [PRIMARY];
GO

CREATE TABLE dbo.Device_new(
      DeviceID int NOT NULL
        CONSTRAINT PK_Device_new PRIMARY KEY CLUSTERED ON [PRIMARY]
    , DeviceName varchar(30) NOT NULL
    );
CREATE INDEX idx_Device_new_DeviceName ON dbo.Device_new(DeviceName) ON [PRIMARY];
GO

CREATE PROCEDURE dbo.RefreshDevice
AS

SET XACT_ABORT ON;

BEGIN TRY

    TRUNCATE TABLE dbo.Device_new;

    --load dbo.Device_new with new data here

    BEGIN TRAN;

    TRUNCATE TABLE dbo.Device;

    ALTER TABLE dbo.Device_new
        SWITCH TO dbo.Device;

    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;

    THROW;

END CATCH;
GO
Related Question