ansaurus

Question

When would you use GUIDs as primary keys?

Answer 1

+2 A:

When the database isn't centralized or the some of the collection is performed remotely.

kenny 2009-05-22 09:37:00

Answer 2

+2 A:

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.

Petros 2009-05-22 09:38:25

The risk of a GUID clash is one I am willing to take ;)

Colin Desmond 2009-05-22 09:53:28

Yes don't get me wrong. Although the probability of this happening is not 0, GUIDs are very suitable for that kind of use. :-)

Petros 2009-05-22 10:30:54

Just make sure you're not using your GUID's as the CLUSTERED index in SQL Server - they are horrible in that way. Use an INT (IDENTITY) or something like that - much better.

marc_s 2009-05-22 12:38:21

Answer 3

+9 A:

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

John Hunter 2009-05-22 09:44:00

Just to be clear, I'd leave my PK as a GUID for distribution/replication purposes (something we need), but introduce a new non-unique clustered index that may (after replication) contain multiple identical entries. How do I then get SQL Server to use this index when inserting rather than the PK, or is it automatic?

Colin Desmond 2009-05-22 09:51:03

+1 for the separate clustered index. NEVER use a guid as a clustered index, the performance is abismal.

Robin Day 2009-05-22 09:53:35

Just a comment, you could use the new newsequentialid() functionality (assuming Sql Server) that would give you sequentially increasing guids which can be used for clustered indexes. Randomly distributed data (clustered index on newid()) may improve performance if you use partitions where the random locations gives that the data is written to different harddrives.

Simon Svensson 2009-05-22 11:29:25

Or find some natural data to use as the clustered index: for example the 'company name' or 'location' or something like that.

ChrisW 2009-05-22 11:54:38

What good is a separate clustered index if its an unused column?

ck 2009-05-26 07:43:05

Performance mostly. It keeps the size of the all indexes down and it will help prevent index fragmentation you get when using guids as keys.

John Hunter 2009-05-26 08:12:36

ansaurus

tags:

views:

answers:

When would you use GUIDs as primary keys?

related questions