Is there an efficiency benefit to using INT vs GUID for a primary key?

Question

I have a situation where I am considering use of INT (Randomly generated) or GUID as the primary key of a table. I fully realize that a randomly generated INT could create a duplicate but I can easily deal with this and I am prepared to accept this so the chance of a duplicate is not part of the question.

Given that I am okay with the very very small chance of a duplicate INT.

Are there advantages or disadvantages to using INT vs GUID as the primary key or in indexes? Does SQL Server handle one or the other better when it comes to a range search? Does SQL Server have any optimizations for GUID vs INT?

http://stackoverflow.com/a/9728569/1080354 – gotqn Jun 29 '16 at 06:40 — gotqn, Jun 29 '16 at 06:40

score 1 · Accepted Answer · answered Jun 29 '16 at 06:10

If you're concerned with performance, then int will beat out the GUID implementation simply because of the size of the data type. Are you comparing to using the uniqueidentifier type or using a char(32)? Either one will be slower than the int implementation.

GUIDs, however, are a good alternative depending on the type of application that you're building. If you are building something that you want to be able to generate a "unique" identifier prior to touching any state machine, then use GUID.

If you're a database guy, you may find yourself leaning towards an int.

If you're an application developer, you may find yourself leaning towards GUID.

Just a quick question, why not use a sequence?

It just depends.

Hope this helps.

The UNIQUEIDENTIFIER type is what I was thinking of. As far as storage between that and the INT what size difference is there? — Samantha J T Star, Jun 29 '16 at 06:52
uniqueidentifier is 16 bytes and an int is 4. https://msdn.microsoft.com/en-us/library/ms187752.aspx — square_particle, Jun 29 '16 at 13:35

Is there an efficiency benefit to using INT vs GUID for a primary key?

1 Answers1