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
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 724
SQL Frenzy (2/25/2008)
Hi,

Can we have two identity columns on a single table?
If not then how can one insert an identity column in a table having an identity column?

Regards,
Ahmad


The answer is simple: you can only have 1 identity column per table. If what you want with the second part of your question is to move the identity attribute from one column to the other, personally I don't see why one would do such a thing as you already have an identity, then first you have to turn off the identity on your original column and then add another column with identity attribute.

As per article, the best way to go is in the Enterprise Manager or Management Studio in design mode on the table, switch on the identity attribute on the column, copy the SQL that was generated and then save it in a file.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 3022
I have to agree with the previous posters, using the GUI to generate the change script is the best approach. The column being changed stays in the same ordinal position, the script runs inside a transaction, and preserves all the indexes, constraints etc.

You can't have users in the db when you're making this type of change, so you can't avoid having at least some downtime.
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Good tip.

"Keep Trying"
Kavin-1064454
Kavin-1064454
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 74
Thank you very much Mr.Thomas. I came across the same issue ie non- identity to identity type change and your article has helped a me a lot.

But when I have tried it in a temp database it shows the error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'TMP' to data type int.

But again I have tried in another database its working fine.

--drop table tmp
create table tmp
(s_no int,
name varchar(10)
)

insert into tmp
values
(12,'name1')

insert into tmp
values
(13,'name2')

insert into tmp
values
(14,'name3')


ALTER TABLE tmp ADD s_no_II INT IDENTITY(1,1)
ALTER TABLE tmp DROP COLUMN s_no
EXEC sp_rename
'tmp.s_no_II','s_no', 'COLUMN'


Can u brief and how the error can be overcome?

Thanks in advance.
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
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)...

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_SourceTable
(
Id int NOT NULL IDENTITY (1, 1),
Column1 varchar(50) NULL,
Column2 varchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_SourceTable OFF
GO
IF EXISTS(SELECT * FROM dbo.SourceTable)
EXEC('INSERT INTO dbo.Tmp_SourceTable (Column1, Column2)
SELECT Column1, Column2 FROM dbo.SourceTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.SourceTable
GO
EXECUTE sp_rename N'dbo.Tmp_SourceTable', N'SourceTable', 'OBJECT'
GO
COMMIT

If i didnt have another 120gb on my disk, i would not be able to do the above based on diskspace alone.

Very nice and very interesting article.
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
...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. :-D
keycard
keycard
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 140
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
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 724
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
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
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.
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 3022
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.
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