When to use GUID in SQL Server

Question

Possible Duplicate:
GUID vs INT IDENTITY

Every now and then I see a system using GUIDs and I ask myself how much this will impact the performance. I once worked on a system that used GUIDs everywhere, it was written by a 'senior developer' and he always said that it was the best option etc. I ran multiple test to check the performance of GUIDs vs auto increment, auto increment won by seconds per query...

I was wondering in what situations I should use GUIDs instead of multiple primary keys/auto increment.

Answer 1

There are tons of debates/opinions/answers for this question around, some here on SO and many many more if you Google for it.

Answer 2

Guids (uniqueidentifier in MSSQL) are good for keys offering these advantages:

1) You can create the key yourself before inserting an object into the database. This allows you to create master detail of records (foreign key references) of the Nth without first asking the database what the next master record key will be.

2) You can create unique keys per any record system wide, which use useful for ORM databases that use a class hierarchy for entities.

3) You can hardcode guids which to identify specific records. Examples might be master "root" user record, master "agency" agency record (if you create an company/agency hierarchy), predefined lookups (like gender, security role).

About optimization, yes using any normal guids for keys will slow down your database, but you can use database optimized guids (something you'll have to google for). Essentially a database optimized guid makes a guid more sequential, but preserves enough of the random part to keep them unique.