uniqueidentifier vs identity

Question

I noticed asp_membership uses uniqueidentifier, and to me, it seemed like a waste of space, because I could not think of a particular reason to not replace it with identity.

However, SQL Server is not letting me change it to int. It says "Conversion from uniqueidentifier to int is not supported on the connected database server". After googling around, it seems like I would have to break all the relationships etc and then manually delete the column and re-add it as int. Do you guys know of a better approach?

I don't think I would be dealing with multiple databases, so uniqueidentifier seems unneeded for me. Do you agree?

PLEASE NOTE: I am starting out a new web application. Do you still think fixing this would be THAT hard?

Also, note that any of my primary keys would not be part of my URL.

Answer 1

My advice would be to leave it alone. You're right about the fact that you would need to go to a lot of work (as you described) to get rid of it. Why? Why mess with something that just works? To save space in the database? With as cheap as storage is these days, and the average size of a hard drive or storage array, you're talking about a completely insignificant amount of space savings.

There is no Return on Investment for this idea.

Answer 2

You can't convert a UNIQUEIDENTIFIER column to INT - here's what you'd need to do in this case is

• add your new ID column as INT IDENTITY - this will create and fill the column with values
• drop the old GUID column you don't need anymore

Of course, since the "UserID" is the primary key, and thus will be referenced from a lot of places, you'll have to do a lot of housekeeping before being able to drop the UserId column.

While I applaud your idea and realization of UNIQUEIDENTIFIER being a really bad choice for a primary and clustering key in a SQL Server table, I think in this particular case, I'd probably leave it "as is" - trying to convert that will cause lots of ripple changes all throughout the ASP.NET tables - probably just not worth the effort.

Marc