April 14, 2009 at 2:57 pm
...but then again, assuming that there are no real concerns (small table, dev database, etc.) with using the "create destination, load destination, drop source, and rename destination" table approach, I'd probably use the GUI as well. 😀
May 8, 2009 at 5:11 pm
If you batch your delete and output it into the destination table, you don't have to set identity_insert on. At least thats what I'm seeing. I did put in a post about the OUTPUT function asking if it sets the identity_insert of a table to on or not. I've not heard anything yet. Over all, I thought this post was very useful.
Thanks
JN
May 9, 2009 at 8:57 am
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.:-)
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 9, 2009 at 10:56 am
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.
May 9, 2009 at 10:59 am
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.
May 9, 2009 at 11:22 am
True true, does seem like a whole lotta effort, but it could come in handy one day..
In my hypothetical disk space concern, good point on the backups, but I guess i was assuming that full and tlog backups are already utilizing space on the drive, so having another 120gb on top of that would be recommended, but not always the case...and there is always LiteSpeed which would compress your full backup to 24gb @ 80% compression. 🙂
May 9, 2009 at 4:22 pm
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
May 10, 2009 at 7:37 pm
I would have thought that too, except he proved the performance gain by the IO stats when doing the switch verses the typical way.
September 13, 2009 at 7:10 pm
There is no coping of data with the switch statement, the “switch” only changes the pointers. But if you have a merge or split involved you are definite to have a lot of IO and this approach needs to reconsidered under the new circumstances (You need to allocate time for downtime).
This approach will work best for databases on a single file group
May 11, 2010 at 6:25 pm
I know I'm a little late to this thread. Its an excellent article. One question, does anyone know why the author wrote in the Disadvantages section:
"It’s only available in Enterprise Edition and Developer Edition."
I've tried ALTER TABLE .. SWITCH TO .. with SQL 2005 Standard and it appears to work. But since this is a new topic to me, perhaps I'm missing something.
TIA!
-L
September 28, 2010 at 3:18 pm
I've got a question. In Method 2, you've got:
CREATE TABLE DESTINATION_TB(
ID INT IDENTITY(125006,1) NOT NULL ,DATE DATETIME ,COST MONEY)
INSERT INTO DESTINATION_TB (ID, DATE, COST) SELECT ID ,DATE, COST FROM SOURCE_TB
DROP TABLE SOURCE_TB
EXEC sp_rename 'DESTINATION_TB' ,'SOURCE_TB'
But won't that fail, because you're trying to insert into an IDENTITY column the value from the old SOURCE_TB?
Kindest Regards, Rod Connect with me on LinkedIn.
September 29, 2010 at 10:56 am
I found that I had to set the IDENTITY_INSERT to ON, before I did the INSERT. Then I turned it back off again, which is the default.
To make it painfully obvious, what I did was this:
SET IDENTITY_INSERT [DESTINATION_TB] ON
then I did the insert as was specified in the article. After doing the insert I did the following:
SET IDENTITY_INSERT [DESTINATION_TB] OFF
Kindest Regards, Rod Connect with me on LinkedIn.
January 11, 2013 at 4:16 am
We can not go with the first method. Because, We will loose the old primary key values and child tables relation with those values.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply