34

For SQL server is it better to use an uniqueidentifier(GUID) or a bigint for an identity column?

Aaron Fischer
  • 20,853
  • 18
  • 75
  • 116
  • 5
    Did you mean primary key? You can't have a GUID identity column. – Rob Boek Feb 02 '09 at 21:10
  • Jeff Atwood has already answered everything [in this comprehensive post](http://blog.codinghorror.com/primary-keys-ids-versus-guids/). – Peter J Feb 02 '09 at 21:12

10 Answers10

53

That depends on what you're doing:

  • If speed is the primary concern then a plain old int is probably big enough.
  • If you really will have more than 2 billion (with a B ;) ) records, then use bigint or a sequential guid.
  • If you need to be able to easily synchronize with records created remotely, then Guid is really great.

Update
Some additional (less-obvious) notes on Guids:

  • They can be hard on indexes, and that cuts to the core of database performance
  • You can use sequential guids to get back some of the indexing performance, but give up some of the randomness used in point two.
  • Guids can be hard to debug by hand (where id='xxx-xxx-xxxxx'), but you get some of that back via sequential guids as well (where id='xxx-xxx' + '123').
  • For the same reason, Guids can make ID-based security attacks more difficult- but not impossible. (You can't just type 'http://example.com?userid=xxxx' and expect to get a result for someone else's account).
Metro Smurf
  • 37,266
  • 20
  • 108
  • 140
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
8

In general I'd recommend a BIGINT over a GUID (as guids are big and slow), but the question is, do you even need that? (I.e. are you doing replication?) If you're expecting less than 2 billion rows, the traditional INT will be fine.

Andrew Rollings
  • 14,340
  • 7
  • 51
  • 50
4

Are you doing replication or do you have sales people who run disconnected databses that need to merge, use a GUID. Otherwise I'd go for an int or bigint. They are far easier to deal with in the long run.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
2

Depends no what you need. DB Performance would gain from integer while GUIDs are useful for replication and not requiring to hear back from DB what identity has been created, i.e. code could create GUID identity before inserting into row.

dove
  • 20,469
  • 14
  • 82
  • 108
1

There can be few more aspects or requirements to use GUID.

  • If the primary key is of any numeric type (Int, BigInt or any other), then either you need to make it Identity column, or you need to check the last saved value in the table.
  • And in that case, if the record in foreign table is saved as transaction, then it would be difficult to get the last identity value of primary key. Like if IDENT_CURRENT is used, then will be again effect performance while saving record in foreign key.
  • So in case of saving the records as for transactions, then it would be convenient to firstly generate Guid for primary key, and then save the generated key (Guid) in primary and foreign table(s).
Arif
  • 11
  • 1
1

If you're planning on using merge replication then a ROWGUIDCOL is beneficial to performance (see here for info). Otherwise we need more info about what your definition of 'better' is; better for what?

Greg Beech
  • 133,383
  • 43
  • 204
  • 250
1

Unless you have a real need for a GUID, such as being able to generate keys anywhere and not just on the server, then I would stick with using INTEGER-based keys. GUIDs are expensive to create and make it harder to actually look at the data. Plus, have you ever tried to type a GUID in an SQL query? It's painful!

Paul Lefebvre
  • 6,253
  • 3
  • 28
  • 36
  • 15
    No one tries to type a GUID. I am sure copy/paste was invented the day after GUID's were invented ;-) – Craig Feb 02 '09 at 21:19
0

It really depends on whether or not the information coming in is somehow sequential. I highly recommend for things such as users that a GUID might be better. But for sequential data, such as orders or other things that need to be easily sortable that a bigint may well be a better solution as it will be indexed and provide fast sorting without the cost of another index.

Chris J
  • 2,160
  • 4
  • 16
  • 23
  • 1
    You can use NEWSEQUENTIALID() to create sequential GUID field which make the index more optimized and improve performance. – Craig Feb 02 '09 at 21:17
0

It really depends whether you're expecting to have replication in the picture. Replication requires a row UUID, so if you're planning on doing that you may as well do it up front.

Jeffrey Hantin
  • 35,734
  • 7
  • 75
  • 94
0

I'm with Andrew Rollings.

Now you could argue space efficiency. An int is what, 8 bytes max? A guid is going to much longer.

But I have two main reasons for preference: readability and access time. Numbers are easier for me than GUIDs (since I can always find the next/previous record easily).

As for access time, note that some DBs can start to have BIG problems with GUIDs. I know this is the case with MySQL (MySQL InnoDB Primary Key Choice: GUID/UUID vs Integer Insert Performance). This may not be much of a problem with SQL Server, but it's something to watch out for.

I'd say stick with INT or BIGINT. The only time I would think you'd want the GUID is when you are going to give them out and don't want people to be able to guess the IDs of other records for security reasons.

MBCook
  • 14,424
  • 7
  • 37
  • 41