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 Wednesday, February 20, 2008 9:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, February 21, 2008 4:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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
Posted Thursday, February 21, 2008 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 21, 2008 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, February 21, 2008 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:29 AM
Points: 266, Visits: 2,610
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
Posted Thursday, February 21, 2008 4:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 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
Posted Thursday, February 21, 2008 4:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Sunday, May 18, 2014 6:11 PM
Points: 110, Visits: 472
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
Posted Monday, February 25, 2008 12:09 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:38 PM
Points: 1,475, Visits: 1,640
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
Posted Monday, February 25, 2008 5:05 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: Thursday, November 20, 2014 4:22 PM
Points: 752, Visits: 918
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
Posted Monday, September 15, 2008 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 30, 2013 4:38 AM
Points: 4, Visits: 119
doesnot work with me nothing affect the identity of the table dont get off
Post #569867
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse