Sql-server – Guid vs INT – Which is better as a primary key

performanceprimary-keysql serveruniqueidentifier

I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

_{( Guid = UniqueIdentifier ) type on SQL Server}

Best Answer

This has been asked in Stack Overflow here and here.

Jeff's post explains a lot about pros and cons of using GUID.

###GUID Pros

Unique across every table, every database and every server

Allows easy merging of records from different databases

Allows easy distribution of databases across multiple servers

You can generate IDs anywhere, instead of having to roundtrip to the database, unless partial sequentiality is needed (i.e. with newsequentialid())

Most replication scenarios require GUID columns anyway

###GUID Cons

It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful

Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')

The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL Server 2005+) and to enable use of clustered indexes

If you are certain about performance and you are not planning to replicate or merge records, then use int, and set it auto increment (identity seed in SQL Server).

Related Solutions

Sql-server – Why use an int as a lookup table’s primary key

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.

Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:

create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go

Run this and you get:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.

This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.

Performance of multi field primary key or ‘contrived’ ‘semi artificial’ key

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

Some natural keys are useful in their own right: ISO currency and country codes
A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Best Answer

Related Solutions

Sql-server – Why use an int as a lookup table’s primary key

Performance of multi field primary key or ‘contrived’ ‘semi artificial’ key

Related Question