joining tables on aspnet_Users.UserId

Iwant to join my existing tables such as ORDERS with aspnet_Users.UserId on ORDERS.CustomerId.
But I am confused which data type should I choose for customerId field.
Thedatatype of the of aspnet_Users.UserId is uniqueidentifier, butobviously I cannot assign my customerId field to uniqueidentifier datatype. I found that "The uniqueidentifier data type in SQL Server isstored natively as a 16-byte binary value." I wish aspnet_Users.UserIdwould be an integer IDENTITY field. I also want to ask if I can changeUserId field's datatype to integer?

There are also some other tables that I want to join with aspnet_Users table on its UserId field. Can you suggest any solutions?

alekhine_x9:

obviously I cannot assign my customerId field to uniqueidentifier data type.

Change "cannot" to "should", then you are correct.

|||Hi Motley, then how can I be assure of whether the aspnet_Users.UserId and MyTable.CustomerId will be same since "uniqueidentifier" will be different for every row.|||

uniqueidentifier is not like IDENTITY in that it will automatically generate a value for you. It's more like a very big int, in that it only stores what you tell it to. If you want it to contain the same value, get the value you want from the customer table, and store it in teh row you want.

|||

thanks, you are right, I must be in a very confused mood not to think that:)

I wonder does it affect performance much making joins on a uniqueidentifier instead of int. I know joins on integer value fields are faster than character based data types.

|||

Does it affect performance? Yes.

Much? No.

Joining on an int, is basically a two instruction compare and conditional jump.

Joining on an uniqueidentifier is 8 instructions (4 compare/conditional jump combinations) on 32-bit machines, 4 instructions on 64-bit machines.

Joining on a varchar/char/nvarchar/nchar is essentially a string compare loop, one operation per byte -- load up two pointers, clear flags, iterate once per byte in the shortest string, and a conditional jump. For long strings at best, it'd be a register copy, and AND operation, a shift right operation, clear flags, a 64-byte string compare loop (one iteration for every 4 bytes), followed by 1-3 8-byte string compare iterations, and a conditional jump. It gets even more complex, as for each character in the string, you have to do an index lookup to get the right collalition order if it's a < or > join, or if multiple characters are considered "equal" -- like "m" and "M".

Although, the compare portion of the joins is not usually the most intensive part of the actual join operation, so while the compare itself is 2-4 times as slow, the actual performance hit is usually negligable.