SQL Server Unique Identitifier vs Integer

Question

Hi all,

I've been learning ASP.net, and been using the membership system. When it auto generated the tables, I was quite suprised to see it uses a field type called 'uniqueIdentifier' as a primary key, when for many years I have been using an integer field set to be an identity that auto increments.

What is the difference (if any at all) between these two methods, and why does .NET appear to favour the unique identifier field?

Thanks for any info!

Tom

Answer 1

I'd say that .NET doesn't favour the uniqueidentifier or guid as an id, but this particular implementation (the ASP.NET SQL Server membership provider) does. I suspect that those who developed the database were working with the assumption that the db usage wasn't to be for high traffic sites, or where heavy reporting was likely to be done.

Perhaps they were trying to avoid any problems with integrating in an existing application, or a future scenario whereby your application had a key for a user. This could be any kind of key for any entity (PK, UserNumber, etc). In the ASP.NET SQL Server implementation, the likelihood of having a collision is very low/approaching zero.

The one drawback that I've learned is that having a clustered index on a guid doesn't scale to large volume databases.

I'm largely in the integer-as-PK camp. They're small, use few bytes, and work very well when your database needs to scale.

Answer 2

What is the difference (if any at all) between these two method

for one a uniqueidentifier is 16 bytes while an int is 4 bytes. IF you have a URL like

http://bla.com?UserID=1

you can easily guess what someone else's userid is so you can try 2 or 4 etc etc

when you have this as UserID C7478034-BB60-4F5A-BE51-72AAE5A96640 it is not as easily and also uniqueidentifiers are supposed to be unique accross all computers

if they use NEWID() instead of NEWSEQUENTIALID() then they will get fragmentation and page splits, take a look at Best Practice: Do not cluster on UniqueIdentifier when you use NewId

Answer 3

The uniqueidentifier type is SQL's Guid type (the corresponding BCL type is System.Guid). In concept, Guids represent a random 128-bit number that is supposed to be unique.

While Guid's have their detractors (comparing guids is, strictly speaking, slightly slower than comparing ints), their random nature makes them helpful in environments like replication, where using an incrementing key can be difficult.