[ACCEPTED]-Is it a bad idea to use GUIDs as primary keys in MS SQL?-primary-key

Accepted answer
Score: 25

There are pros and cons:

This article covers everything.

GUID 1 Pros

  • Unique across every table, every database, 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
  • 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 2005) and to enable use of clustered indexes
Score: 17

I wrote a post about this last week with 4 some code to show you what happens: Some Simple Code To Show The Difference Between Newid And Newsequentialid

Basically 3 if you use newid() instead of Newsequentialid() you 2 get horrible page splits if your PK is a 1 clustered index (which it will be by default)

Score: 16

Personally, I'd use an int or bigint for 4 the PK, but just put in another "Guid" column 3 for those situations where you need an unguessable 2 "key" for that record, and generate the 1 Guid when you insert the row.

Score: 5

It will be bad if you will need to do joins 4 over large sets (let's say 100,000ths). Been 3 there, suffered that.

Later Edit : I also 2 encountered an even worse screw-up (can't 1 call it "approach") : storing GUIDs in char(36) columns!!

Score: 3

A GUID is a powerful datatype for identifying 14 a row, since it is almost guarenteed to 13 be unique, this allows a lot of flexibiliy 12 for example you can generate the Guid in 11 the application tier which can greatly simplify 10 saving your relationships.

As was said the 9 big downside is the page splits which will 8 occur if your PK is a clustered index; however, you 7 can solve this by two ways. You could use 6 the NewSequentialId() or you can set the 5 PK to be non-clustered. I'd recommend you 4 build your database based on your data requirements, and 3 if you need a GUID use it, and then optimize 2 around it. And validate its performance 1 in your environment.

Score: 3

Jimmy Nilsson wrote a fantastic article about GUIDs vs. INTs and combined GUIDS. Conclusions...don't fear 1 the GUID...well composite guids anyway.

Score: 1

It is bad if you want to do fast writes 3 to the database. If you are going to do 2 massive insertions then it is better to 1 use a different datatype.

Score: 1

It is a "bad" idea. It can be slow. You 12 it isn't really good for fast searching 11 with indexes either. The only real time 10 that we use GUID or UniqueIDs is when we 9 have to keep data items connected across 8 databases, typically when we have a server 7 database and a local database for an application 6 (for disconnected systems). You really have 5 no other way to keep things together other 4 than guids. For indexes and primary keys, you 3 want to use an integer value and try to 2 link things with association tables rather 1 than using guids all over the place.

Score: 1

The best reason I have found for using them 6 is when replicating databases. Even then, there 5 can be better ways of handling that like 4 multi-part primary keys that include ID 3 and maybe location. If you don't need that 2 capability, I suggest sticking with some 1 form of integer.

Score: 0

It can also really slow down your database 5 reads because primary keys are created with 4 clustered indexes unless told otherwise. A 3 guid is the worst type to have as your clustered 2 index and a int or bigint would serve as 1 a better primary key or clustered index

Score: 0

I wouldn't say its bad, per se. I've talked 6 to a lot of people who swear by it, although 5 I find it a little verbose. I would agree 4 with routeNpingme and say you should use a regular integer 3 for the ID but also include a GUID (which 2 you need for replication anyway) "just in 1 case"

More Related questions