the sqlist (5/9/2009)
jeremygiaco (4/14/2009)
I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...The problem is that there is no other way of doing it and the article actually does the same thing, copies the original table to a new table. Even when you do a simple ALTER TABLE ADD ... IDENTITY in the background happens the same thing, copy the table, and that is why the GUI generating script does the same.
If you really know a different way I would like to see it.:-)
As the author stated, there are many tasks to get the SWITCH to work . . .
* Drop all the Foreign Keys referencing to SOURCE_TB if any
* Alter all the objects with schemabinding if any
* Drop all the Indexes from SOURCE_TB
* Create the new table DESTINATION_TB with same schema as the SOURCE_TB
* SWITCH the data between the tables
* Drop the table Source_TB
* Rename the table Destination_TB to Sourse_TB
* Recreate all the Foreign Keys to refer to SOURCE_TB
* Recreate all the Indexes on SOURCE_TB
* Recreate functions, views with SCHEMABINDING
* Create all other constraints
. . . and the GUI will generate all of those changes for you, and works regardless of which edition you're using.
I would also like to point out that if you run out of disk space whilst copying a 120 GB table then you need a much larger disk, because you certainly don't have enough space for your database & log backups.