Re: Auto-incrementing key or GUID for new DB ?

Hi Richard

So far as I know, GUIDs are unique ON a system, not across them. I've seen where a GUID generated on one system collides with one generated on another system to which the original table was transferred.

Best practice, as I understand it, is to have a single server that generates GUIDs for all systems across a network.

Having said that, I'm sure there is more I've missed!

HTH
Vern

On 1/25/2016 3:27 PM, Richard Schoen wrote:

Since there was another thread on auto-incrementing keys I thought I would throw out a discussion question on new DB design.

If you are doing a new database would you use an auto-incrementing primary key or GUID for a unique identifier as a primary key ?

There seem to be reasons for both if you google this topic on the web.

Couple initial thoughts.

Auto-incrementing number as primary key

Auto-incrementing key for:
-Easy to set up and next number just happens

Potential con:
-It seems that renumbering or collissions can possibly occur when replicating or copying tables which seems like it could mess up table joins to related files.
-If you use the key value in a URL link without obfuscation or encryption, your app could potentially be easily hacked by modifying URL entries.

GUID/UUID as key

Using GUID/UUID for:
-Appears to be unique across systems
-Should be replicable without key collisions
-Harder to hijack an app URL with the key in it.

Potential con:
-Not sequential and 32 bytes so supposedly can cause indexing performance issues. (At least purportedly on SQL Server)
-More disk space
-Harder to debug app code or data issues because of long key values.

Looking forward to your insight.

Regards,

Richard Schoen | Director of Document Management Technologies, HelpSystems
T: + 1 952-486-6802
RJS Software Systems | A Division of HelpSystems
richard.schoen@xxxxxxxxxxxxxxx<mailto:richard.schoen@xxxxxxxxxxxxxxx>
www.rjssoftware.com<http://www.rjssoftware.com/>
Visit me on: Twitter<https://twitter.com/richardschoen> | LinkedIn<http://www.linkedin.com/in/richardschoen>