Changing a Non-IDENTITY column to IDENTITY and vice versa

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Thomas Pieries » Changing a Non-IDENTITY column to IDENTITY...

28 posts, Page 1 of 3

1 2 3 »»»

Changing a Non-IDENTITY column to IDENTITY and vice versa

Rate Topic

Display Mode

Topic Options

Author

Message

Thomas-428301

Posted Wednesday, February 20, 2008 9:59 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:15 AM
Points: 17, Visits: 149

Comments posted to this topic are about the item Changing a Non-IDENTITY column to IDENTITY and vice versa

Post #458355

Ian Yates

Posted Thursday, February 21, 2008 4:24 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

Good tip :) I must admit that I'll still probably stick with letting the GUI tools do all the ugly scripting for me as most of our customers don't have SQL Enterprise (and obviously aren't running developer).

Post #458458

jdoherty-609236

Posted Thursday, February 21, 2008 5:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2008 9:03 AM
Points: 1, Visits: 3

I am on the business analyst side.....are you speaking of de-ideintification or masking.....?

Post #458501

Steve Reich

Posted Thursday, February 21, 2008 7:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 14, 2011 7:37 AM
Points: 106, Visits: 51

Clever use of partition switching!

Note that BOL warns that the transfer could introduce duplication in the identity column and recommends running DBCC CHECKIDENT (presumably after the switch).

I recommend further reading BOL's article "Transferring Data Efficiently by Using Partition Switching" also available online in
Technet: http://technet.microsoft.com/en-us/library/ms191160.aspx

Post #458540

JJ B

Posted Thursday, February 21, 2008 9:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 255, Visits: 2,407

I appreciated the idea in this article.

I thought I would also share another option. I routinely re-create my databases from scratch when I need to make major schema changes to the database. This identity change would count as such a change. The steps are:

1) make changes to the data model as needed
2) forward engineer a new database that is just like the original db only it has the schema changes you want.
3) run all the code (stored procs, triggers, etc.) on new db
4) run some code that automatically creates the code for a stored proc which copies table data from the source database into the new database
5) tweak the copy code as needed and run it
>> At this point, the new database is identical to the old database except for the changes you have deliberately made. So:
6) backup the new database and restore over the original database.

The advantages are that you follow the same set of steps every time. You don't have to say look for the set of indexes and foreign keys that are specifically attached to the one table you are working with. Also, you can make a whole bunch of minor and major changes to your database at once with the same set of steps. One of my favorite advantages is that you make sure your database matches your data model, and you know that your source code (stored procs, functions, etc.) are all up to date. In other words, you know that you can re-create the schema for the production database as needed at any time. The parallel is like always having a fully working build at your fingertips as application developers try to do.

All of the steps above have to be done when no one is using the database. Thus, this option may not be practical for larger databases and longer up-times. I have relatively small databases (under a gigabite) and up-time is only 7-6 on weekdays. So, it is easy for me to have "down time" and work on weekends. For other agencies in my situation, this is an approach to think about for any type of significant schema change--and it works in any version of SQL Server.

Post #458647

Sean Zhang-369248

Posted Thursday, February 21, 2008 4:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2008 4:33 PM
Points: 4, Visits: 42

Thomas,
I like the technique with partition switching. Thank you.
BTW, for your method 2, you will need
SET IDENTITY_INSERT Destination_TB ON;
before the insert.

\Sean

Post #458870

dr.kusnadi

Posted Thursday, February 21, 2008 4:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:05 PM
Points: 110, Visits: 439

Hi Thomas,

isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?

or it might be just me missing the point.

Cheers,
DK

Post #458872

Sqlfrenzy

Posted Monday, February 25, 2008 12:09 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 1,431, Visits: 1,540

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

Regards,
Sqlfrenzy

Post #459578

timothyawiseman

Posted Monday, February 25, 2008 5:05 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

Daniel R. Kusnadi (2/21/2008)

Hi Thomas,

isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?

or it might be just me missing the point.

Cheers,
DK

I have to agree with Daniel. The article is well written and well thought out, but in most case where there are no complicating factors it seems easiest just to use design table. Now, as can be seen by generating the change script, what this does in effect is very similar to method 2 listed, but in most cases it makes sense to let the gui handle that.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #460026

solidsnake10_7

Posted Monday, September 15, 2008 4:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 6:33 AM
Points: 4, Visits: 116

doesnot work with me nothing affect the identity of the table dont get off

Post #569867

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions