Solved

How to change an increment field in a database table from Int to UniqueIdentifier

Posted on 2015-02-12
7
58 Views
Last Modified: 2015-02-19
I have a table in a database with a data_type of INT that ties into other tables.  I need to change the datatype from INT to UniqueIdentifier for a third party product that requires the use of UniqueIdentifier for creating, editing, and deleting records from a SharePoint list.

How can I make this change to the table without effecting the records that already exist and tie to records in other tables?
0
Comment
Question by:mounty95
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Consider adding a new unique key to this table for their use.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

To expand on efhtebas post - I don't think that you can change that column. I think you will need to add another column with whatever constraints are appropriate, populate this column for your third party product, and as you don't want to affect other tables, leave the existing int identity as it is.

HTH
  David
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
David Todd is right. An uniqueidentifier column is 16byte GUID so you can't convert from INT.
Example of a value for a GUID: 0B984625-E58C-4DF1-9862-E1C87B27AA0
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:mounty95
Comment Utility
All,

So if I am understanding this correctly, I need to do the following:
1.  Create a new column in primary table with a GUID.
2.  Populate that column for all existing records.

And then do I have to create that same GUID column in all of the other tables and update them with the GUID from the primary table or do I just have two numbers being created with every new record and the GUID simply for the third party app and the int for the relation with other tables?

Thank you all for helping me with this.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
You need to use the NEWID() function to get a new GUID.
I'll drop the INT columns and update the related tables with the GUID column.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
Comment Utility
Hi,

On one hand, its a perfect pain to change the ID column from int to GUID, and cascade that to other tables. So on one hand, this is the better solution, on the other it could be a lot of work.

I'd be inclined if time is in short supply to not propagate this change to other tables, and just keep the GUID as an alternate key.

Regards
  David
0
 

Author Comment

by:mounty95
Comment Utility
So I am not sure how I was able to do this in my test environment, but I was able to add a column, give it a uniqueidentifier data type, used the newid() function in the default value or binding column property and was able to get the existing records to populate with a value, but now I can seem to do it with my live environment.  I am not sure what I did to get it to work, but now not work.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now