?
Solved

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

Posted on 2015-02-12
7
Medium Priority
?
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 34

Expert Comment

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

Expert Comment

by:David Todd
ID: 40608451
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 51

Expert Comment

by:Vitor Montalvão
ID: 40613734
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:mounty95
ID: 40616370
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40616381
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 1000 total points
ID: 40617970
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
ID: 40619077
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

752 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