Sample Header Ad - 728x90

Merits of inserting to temp table and renaming versus just adding/removing columns

1 vote
2 answers
1610 views
If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed. When I use RedGate SQL Compare to create conversion scripts its strategy is to create a temporary table, copy all the data into that table, drop the old table, and then rename the temporary table. I assume this cleans up the pages as all the rows were "perfectly" sequentially inserted. I recently had a DBA tell me that this "copy and rename" approach is inefficient, expensive and unnecessary. What are the merits of these two approaches?
Asked by Matthew (1693 rep)
Sep 5, 2014, 02:39 AM
Last activity: Sep 5, 2014, 07:15 AM