Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing a Non-IDENTITY column to IDENTITY and vice versa


Changing a Non-IDENTITY column to IDENTITY and vice versa

Author
Message
JeremyG
JeremyG
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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. Smile
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 724
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
Jeremy Giaco
Jeremy Giaco
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
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.
Thomas-428301
Thomas-428301
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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
lido14
lido14
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1937
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1937
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.
ramkoti.237
ramkoti.237
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search