Difference between uniqueidentifier and PK integer

Question

Difference between uniqueidentifier and PK integer

nymgk_75

SSC Veteran

Points: 233
More actions
April 13, 2009 at 2:05 pm

#199440

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhaskar Basak SSC Eights! Points: 967 More actions · Answer 1

Uniqueidentifier stores GUID value which is unique through out the globe can be inserted by NEWID(). One of the purpose of using uniqueidentifier as PK as per my persional experience is when you may need to query between same tables of multiple databases ( e.g. each for a company each agent has seperate standalone database and at Head office need to query all the databases to get summarised things)

nymgk_75 SSC Veteran Points: 233 More actions · Answer 2

Interesting...so a GUID is unique value across dbs and the world? So regardless if we're using GUIDs in SQL Server 2005...then migrate to 2008, we won't have an issue? Is it best to call NEWGUID() within the ASP.net or is that done within sql server?

Thanks much!

rgillings SSCrazy Points: 2114 More actions · Answer 3

A practical use for using a GUID for a primary key value is when your application needs to generate a primary key value without querying the database, such as for disconnected apps.

A GUID, however, is a far inferior option as a CLUSTERED INDEX, and that scenario should be avoided. An INT IDENTITY PK is much better suited to be a CLUSTERED INDEX.

nymgk_75 SSC Veteran Points: 233 More actions · Answer 4

I've heard folks use GUIDs when passing ID's in URLS. When you have UserID = 100, someone can type in 101 or 99 and display information that way. When you see a large value of 889EA536-0B32-3345-B124-F44141C50CB7 would make it complicated to guess the next record. I guess they'd use an INT as the PK, but meanwhile have a GUID column for each record? Thoughts on that practice?

rgillings SSCrazy Points: 2114 More actions · Answer 5

In a case like that I would typically look for a different field that would be sequential to be used as the CLUSTERED INDEX, such as a CreateDate field, and leave the GUID as the PK.

If you decided to use an int as a PK/CLUSTERED INDEX, then it would be a good idea to add a UNIQUE CONTSTRAINT to the GUID.

Anyone else have different thoughts?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi

I still would use an INT column as primary key. You can use GUIDs as UNIQUE CONSTRAINT column for data uniqueness over different databases/servers/locations. Anyway we use a simple string which is calculated by a database ID and an INT. It's much more readable ;-).

The approach to send user-ids over url's is a very common security issue. "Those folks" should use a session id which can be a GUID and send them as POST information instead of the url. Sure this information can also be sniffed, but it is less simple and due to the fact that the session expires the stability of the GUID is timed.

Greets

Flo

Brad Mullins-359749 SSC Veteran Points: 214 More actions · Answer 7

Here is a good blog entry from Kimberly Tripp explaining in detail the issues caused by using UniqueIdentifiers as your clustered index:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx