Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Changing a Non-IDENTITY column to IDENTITY and vice versa Expand / Collapse
Author
Message
Posted Saturday, May 9, 2009 11:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 11, 2011 10:58 AM
Points: 49, Visits: 100
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. :)
Post #713579
Posted Saturday, May 9, 2009 4:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624
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

Post #713607
Posted Sunday, May 10, 2009 7:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 8:25 AM
Points: 159, Visits: 122
I would have thought that too, except he proved the performance gain by the IO stats when doing the switch verses the typical way.
Post #713820
Posted Sunday, September 13, 2009 7:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:15 AM
Points: 17, Visits: 149
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
Post #787120
Posted Tuesday, May 11, 2010 6:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 11, 2011 2:23 PM
Points: 1, Visits: 26
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
Post #920103
Posted Tuesday, September 28, 2010 3:18 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #994815
Posted Wednesday, September 29, 2010 10:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #995435
Posted Friday, January 11, 2013 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 8, 2013 3:18 AM
Points: 1, Visits: 6
We can not go with the first method. Because, We will loose the old primary key values and child tables relation with those values.
Post #1405882
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse