GUID VS Auto Increment. (In comfortably wise)

A while a go, my sysadmin restored my database by mistake to a much earlier point.

开发者_JAVA技巧After 3 hours we noticed this, and during this time 80 new rows (auto increment with foreign keys dependency) were created.

So at this point we had 80 different customers with the same ids in two tables that needed to be merged.

I dont remember how but we resolved this but it took a long time.

Now, I am designing a new database and my first thought is to use a GUID index even though this use case is rare.

My question: How do you get along with such long string as your ID?

I mean, when 2 programmers are talking about a customer, it is possible to say:

"Hey. We have a problem with client 874454".

But how do you keep it as simple with GUID, This is really a problem that can cause some trouble and dis-communications.

Thanks

GUIDs can create more problems than they solve if you are not using replication. First,you need to make sure they aren't the clustered index (which is the default for the PK in SQL Server at least) because you can really slow down insert performance. Second they are longer than ints and thus take up not only more space but make joins slower. Every join in every query.

You are going to create a bigger problem trying to solve a rare occurance. Instead think of ways to set things up so that you don't take hours to recover from a mistake.

You could create an auditing solution. That way you can easily recover from all sorts of missteps. And write the code in advance to do the recovering. Then it is relatively easy to fix when things go wrong. Frankly I would never allow a database that contains company critical data to be set up without some form of auditing. It's just too dangerous not to.

Or you could even have a script ready to go to move records to a temporary place and then reinsert them with a new identity (and update the identities on the child records to the new one). You did this once, the dba should have created a script (and put it in source control) so it is available the next time you need to do a similar fix. If your dba is so incompetent he doesn't create and save these sort of scripts, then get rid of him and hire someone who knows what he is doing.

just show a prefix in most views. That's what DVCSs do, since most of them identify most objects by a hexcoded hash.

(OTOH, I know it's fashionable in many circles to use UUIDs for primary keys; but it would take a lot more than a few scary stories to convince me)