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

Uniqueidentifier and int are to very different datatypes. You can't just change a guid to an int. You would need to remove all the relationships, assign numeric values to the parent table, add numeric columns to all the child tables, use joins to set all the values in the child tables. Remove all the guid columns, make the int column the primary key. Setup all the relationships.

And not to mention change every app that talks to the database and expects the value to be a guid so that it can work with the integer value.

You are looking at a lot of work for very little gain.

Yes you would get some performance improvement because the guids aren't sequential and the numbers would be. But that really isn't enough of a reason to actually make the change unless you are seeing some major performance issues from this.

At the least you'd be looking on weeks of work and testing. In reality months to ensure that you catch every little possible change.

Answer 3

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