GUID vs. int ID auto-increment

Your experience never having needed anything beyond autoincremental ids might suggest that GUIDs are often a solution in search of a problem. Use them when and if you ever run into a requirement where your familiar pattern doesn't work. The microsoftiness is irrelevant.

The only realistic scenario I've seen is merging tables from two sources.

One problem with GUIDs:

Because they are not sequential, the database will have to work hard to update indexes. With a sequential id, it can usually just append it to the end (more or less). Since GUIDs are random it has to fit it into an existing block. That said, we use GUIDs for some tables and they seem to work fine even under fairly heavy load.

"old, reliable auto-incremented int" depends rather strongly on just how scalable your database needs to be. Auto incrementing stops working in the trivial case when you have a setup with at least two masters. It's not too difficult to work around that, of course, because it's such a common problem; Different database engines may coordinate the sequence between the masters, for instance only one master may allocate from any given sequence.

When you get into sharding the data, it's normally desirable to know the shard from the key. An auto incremented id doesn't contain information about which shard should host that record.

GUID's solve the problem in a different way; two distinct masters have distinct host identifiers (typically a MAC address). Since that is used in computing a new GUID, distinct masters cannot create guid's that collide. Furthermore, since the host is part of the ID, It can be used to directly identify the shard that holds the record.

A third option is to use no surrogate keys at all (neither auto increment integers nor guids).