When would you use GUIDs as primary keys? [duplicate]

Question

Possible Duplicate:
Advantages and disadvantages of GUID / UUID database keys

Are there any circumstances where it is essential to use GUIDs as primary keys in a SQL Server 2005/8 DB. For example, does the use of the MS Sync Framework force this, or data replication?

Answer 1

You would use guids as a key if you needed multiple databases synchronising via replication.

Another reason to use guids is if you wanted to create rows on some remote client eg a winforms app and then submit those to the server via web services etc.

If you do this I would strongly suggest that you make sure that you specify your own clustered index based on an auto incrementing int that is not unique. It can be a considerable overhead inserting rows into a table where the clustered index is a guid.

Update: Here is an example of how to set up a table like this:

CREATE TABLE [dbo].[myTable](
[intId] [int] IDENTITY(1,1) NOT NULL,
[realGuidId] [uniqueidentifier] NOT NULL,
[someData] [varchar](50) NULL,
    CONSTRAINT [PK_myTable] UNIQUE NONCLUSTERED 
    (
   [realGuidId] ASC
    )
)

CREATE CLUSTERED INDEX [IX_myTable] ON [dbo].[myTable] 
(
[intId] ASC
)

You would insert into the table as normal e.g.:

INSERT INTO myTable VALUES(NEWID(), 'Some useful data goes here')

Update: I listened to a really good dotnetrocks episode that talks about this its worth a listen - Show #447

Answer 2

I am using GUIDs as primary keys, because I don't want to have composite primary keys when I am building applications with distributed databases and one central database that is synchronized with data from all the distributed ones. With GUIDs I am sure (almost*) I will not have a conflict (constraint violation) when I pull data from all the DBs into the central one.

* it is highly unlikely having the same GUID generated in two different places, but not impossible.