Difference between datatype uniqueidentifier and int for a primary key?
-
I've noticed that some of the aspnet_ tables for security (e.g. aspnet_Roles) uses a primary key of a uniqueidentifer data type which is a very long number. When should someone use a primary key, where the datatype is a uniqueidentifier as opposed to int and set IsIdentity=Yes? Thanks
-
I've noticed that some of the aspnet_ tables for security (e.g. aspnet_Roles) uses a primary key of a uniqueidentifer data type which is a very long number. When should someone use a primary key, where the datatype is a uniqueidentifier as opposed to int and set IsIdentity=Yes? Thanks
The main reason is because a unique identifier is unique for any database. One advantage is that if you have to merge databases then you won't class with ID values. With an int then the possibility is quite high unless you carry out a lot of work to prevent it. Another is security. Because unique identifiers are randomly generated that means a person who has one identifer cannot easily guess the next on in the sequence or find adjacent rows in the database. They could randomly guess, but with the speed of current technology they'll be there a while.
*Developer Day Scotland - Free community conference Delegate Registration Open