database design problem - convert int to uniqueidentifier
Question
Monday, March 17, 2008 2:24 PM
Hi,
I am working on my database design for a webapplication.
Every table is setup with an id. I have used an identity int.
Now I was looking at uniqueidentifier, newid() and was wondering if this was a better solution.
I have created several relations between my tables etc
Is it possible to convert these identity int's to uniqueidentifiers and use newid() to declare the id ?
Greetings
All replies (7)
Monday, March 17, 2008 3:47 PM ✅Answered
EXACTLY,
1. Delete the relations
2. Remove the identity from the id column
3. Create a new column with Uniqueidentifier
4. Fill those values for the Uniqueidentifier column
5. Don’t remove the id column in any of the table
6. Add the new Uniqueidentifier column in all the child table
7. Use the OLD id column to map your child record & update the new Uniqueidentifier value from your parent table.
8. Drop all the id columns
9. Recreate the relations
Monday, March 17, 2008 3:22 PM
First of all I didn’t get your conclusion changing the integer identity to uniqueidentifier. You already pointed out there are lot of relationship dependent there with that column. In this case you have to manually drop all those relationships. You might need another backup table where you need to store both the identity & integer ids for lockup. As per my understanding you can’t make any alter statement on the existing identity column. So, you have to drop & recreate the column with new identity datatype. (may be you need to recreate the entire table). Once the new table done then you have to map all those child table with new value using the backup lookup table…
& so on….
For these process you might need to bring down your database in offline (single user mode) & you have to do these..
Monday, March 17, 2008 3:25 PM
Any id replacement is possible, but there are implications -- it all depends on the database design, where those ids are used -- do you have foreign key references etc. Would need more information to technically advise you.
More than the question of, "is this possible", because you can replace these values, but why and what will using guids get you over using integers? There are pluses and minuses to both, and this can only be answered by knowing more about the business problem your application is trying to solve.
Just some food for though, and by no means a complete argument for use of either int or guid: I have seen guids used in web applications when the project was taking a .net session id, which are guids, and storing it as the identity for a record -- guids for the most part can be assumed to be globally unique -- in other words the probability of two disparate databases that contain similar business process information will have the same guid is very very very small, and thus can be used as a unique identifier for a set of records and merged later for reporting, auditing, etc. Integers consume less database space than guids, less space used on your data row, and thus make for a more compact database design, particularly for warehousing or large OLTP applications.
Hope that gives you something to chew on, post more info or let me know if this gives you enough to make a decision.
Monday, March 17, 2008 3:31 PM
I wanted to change to uniqueidentifier because I thought it would be more secure then a "guesable" int identity. Isn't this so ?
It's a very basic webapplication actually.
It's a Resume management system that will be used internally in a company.
I hava a couple tables where I wanted to use the GUID,
f.e. in the users (profiles) and resume's tables.
The database isn't being used in production, still in the very early testing stage.
Has only a couple of records.
I would probably need to delete all relationships and recreate those, right ?
Monday, March 17, 2008 4:06 PM
Manivannan's steps are exact and get your replacement of ints to guids exactly -- but I guess I would follow this up with some more thought on why you want to move to guids. The scenario you have doesn't necessarily require guids for identifiers in your dataset -- keep in mind for optimization of web or windows apps that the less data over the pipe, the better. I would recommend keeping your integers instead of switching to guid.
Here is a good blog post and guids as identity in your app and the size/query costs to consider:
http://www.sqljunkies.com/WebLog/odds_and_ends/archive/2005/08/31/16595.aspx
A good Microsoft Press book that covers database design is the 70-441 book that preps you for the exam. There is a lot of good information in this exam prep book covering the different aspects of database design.
Monday, March 17, 2008 7:53 PM
Thanx for the help everyone
I'm still wondering if i really need GUID after reading these posts.
It's just that I see it being used a lot on professional sites, then there must be a good reason why these sites use this technique.
Greetings
Tuesday, March 18, 2008 12:08 AM
Every application/business process has their own reason for using guids -- but just because someone is using it doesn't mean the usage is correct -- be careful about what you model your process after; there are many ways to technically program an application, but it all depends on what you need to have happen and where it needs to be down the road.
From your situation as described, I do not see any reason for your application to use guids in the design -- but feel free to post if you have any additional questions.