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)
OR
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.
No comments:
Post a Comment