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 Friday, April 10, 2009 8:07 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 136, Visits: 633
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

Post #694845
Posted Friday, April 10, 2009 9:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:23 AM
Points: 1,522, Visits: 2,731
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.
Post #694909
Posted Monday, April 13, 2009 2:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Good tip.

"Keep Trying"
Post #695584
Posted Monday, April 13, 2009 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 31, 2010 11:20 PM
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.
Post #695790
Posted Tuesday, April 14, 2009 2:31 PM


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
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.
Post #697035
Posted Tuesday, April 14, 2009 2:57 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
...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.
Post #697068
Posted Friday, May 8, 2009 5:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 9:42 AM
Points: 228, Visits: 115
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
Post #713424
Posted Saturday, May 9, 2009 8:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 136, Visits: 633
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

Post #713545
Posted Saturday, May 9, 2009 10:56 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
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.
Post #713575
Posted Saturday, May 9, 2009 10:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:23 AM
Points: 1,522, Visits: 2,731
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.
Post #713576
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse