SQL Clone
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
Thomas-428301
Thomas-428301
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 149
Comments posted to this topic are about the item Changing a Non-IDENTITY column to IDENTITY and vice versa
Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 445
Good tip Smile 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).



jdoherty-609236
jdoherty-609236
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 3
I am on the business analyst side.....are you speaking of de-ideintification or masking.....?
Steve Reich
Steve Reich
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 52
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
JJ B
JJ B
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 2860
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.
Sean Zhang-369248
Sean Zhang-369248
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
dr.kusnadi
dr.kusnadi
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 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
Ahmad Osama
Ahmad Osama
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2055 Visits: 1660
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
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1474 Visits: 920
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/
solidsnake10_7
solidsnake10_7
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 121
doesnot work with me nothing affect the identity of the table dont get off
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