JeremyG (5/9/2009)
I will probably continue to use the create table/copy data/rename tables approach 99% of the time myself..but i think the article does offer a different way to do this..the code was:CREATE TABLE DESTINATION_TB( ID INT IDENTITY(125006,1) NOT NULL ,DATE DATETIME ,COST MONEY)
ALTER TABLE SOURCE_TB SWITCH TO DESTINATION_TB
DROP TABLE SOURCE_TB
EXEC sp_rename 'DESTINATION_TB' ,'SOURCE_TB'
If I understand his logic correctly, it's not quite the same thing since you're only changing the pointers, so you never really have to copy all that data. I would say that the IO stats speak for themself in this case.
Correctly if I'm wrong but the trick with switching doesn't matter it still has to exist a transfer of data BECAUSE OF THE IDENTITY. If it wasn't for the IDENTITY maybe not. What I thing happens is switching the pointers and then apply the IDENTITY, which actually means copy of data.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist