Monday, October 13, 2025

Move SQL Server Data Instantly -- ALTER TABLE SWITCH

Use ALTER TABLE ... SWITCH to move very large tables instantly.  Yes.  I said instantly.  ALTER TABLE .. SWITCH doesn't copy the data or physically move it.  It just reassigns the page ownership.  This means that only the metadata with the data pointer changes, and that's why it completes in milliseconds and barely touches the transaction log.

Why DBAs might use SWITCH

  • Replace a bad load (month/year) in seconds.

  • Archive or purge without huge deletes.

  • Make schema changes on large tables like remove IDENTITY property, go from INT to BIGINT, or change compression with zero downtime.

  • Stage and validate offline, then promote instantly.

Requirements

  • Your tables match:   Same columns -- names, order, types, nullability, same indexes clustered and non, same compression, same computed columns, collation, filegroups... everything.  
  • Target table is empty before the SWITCH
  • Foreign Keys / constraints must be compatible (or you can temporarily drop them and recreate afterward)


Example --  To remove IDENTITY column

-- Original table with IDENTITY
CREATE TABLE dbo.Orders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATE NOT NULL,
Amount MONEY NOT NULL
);
 

-- Target clone WITHOUT identity (same shape)
CREATE TABLE dbo.Orders_NoIdentity
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate DATE NOT NULL,
Amount MONEY NOT NULL
); 

-- Batch copy + wait + watch log file + make dinner + wait longer
INSERT dbo.Orders_NoIdentity (OrderID, OrderDate, Amount)
SELECT OrderID, OrderDate, Amount
FROM dbo.Orders;

          OR 

-- Instant handoff
ALTER TABLE dbo.Orders
SWITCH TO dbo.Orders_NoIdentity; -- completes instantly


Serious.  It is that easy.  I, too, was totally Mrs. Doubtfire the first time I did it on a table with 633,910,472 records.  I kid you not.  It was instant.  I literally jumped in my seat!  Looked around me hoping that someone else might have seen it.  Checking my logs, SELECTing COUNT... I was floored. 

ALTER TABLE ... SWITCH is also regularly used with data partitions.  In fact, that is where I first learned about it years ago, from my very favorite MSFT expert.  She taught me how to manage the RANGE RIGHT partitions quarterly in a rather large OrderEvents database using ALTER TABLE ... SWTICH PARTITION.  Very good, very effective, but I'm just saying that you don't HAVE to have partitions to use this feature.  

You should try this yourself on any table - big or small.  Make sure your source and target table definition matches and your target is empty.  Then watch the magic happen.  Let me know how many rows you 'moved' in less than a second.




No comments:

Post a Comment