The actual problem
The problem the developers are trying to solve is a fairly standard migration problem, from one data type to a new one. They have a new problem that was not anticipated at the time the database was originally designed; namely, they now need to synchronize data across databases. This tends to be a costly endeavor, particularly if the data types are relied upon in a lot of code. Looking for cost saving measures is not wholly unreasonable.
Math says no
First of all, it's important to realize that the problem is probably mathematically intractable. A GUID or UUID is just a 128 bit, or 16 byte, number. The size of an
IDENTITY column depends on the data type used, but they are typically
INT (32 bits, 4 bytes); sometimes
BIGINT (64 bites, 8 bytes) is used. There is no mathematical way to fully map the range of GUIDs to the range of INTs or even BIGINTs. It's mathematically possible if the column is
DECIMAL(38,0), which is a big enough, but this is highly uncommon.
Even if it's possible to map GUIDs to the
DECIMAL type, that doesn't mean it's practical. Virtually no one does this, so you're going to have to spend time (=money) ensuring that the mapping works correctly. Their solution introduces a not insignificant risk of creating strange and hard to diagnose bugs.
Additionally, you're not going to preserve the existing IDs of the data with their solution. This may well break any bookmarks end users have that include the IDs.
Finally, their solution is likely to be entrenched. It's not a good practice because of all of the above reasons, but if they get it, chances are they won't work on moving away from it any time soon because it's too "easy" to just keep using the integer keys in all new code.
A more standard approach
A relatively standard approach to introducing synchronization to an existing system is to add a new unique ID. This new ID is placed in the data in addition to the old, existing keys. Then surrogate keys are not synchronized.
This has some major benefits:
- Solves the problem of enabling synchronization across databases.
- Existing code that relies on the old keys doesn't have to change. (Definitely not in the short term, and possibly not ever.)
- No mathematically impossible mappings.
- Existing key values are preserved.
There's two minor annoyances with this approach:
- Since surrogate keys are not synchronized, if surrogate keys appear in code and particularly in the application, then these IDs will be different across different databases. For your specific situation (synchronizing to test and development copies of the database, rather than some kind of replication across multiple production databases), this is only a minor annoyance. However, it's also one that can be solved: as the need becomes more apparent through whatever inefficiencies this creates, the developers can adjust specific, targeted pieces of code to use the new synchronization ID as needed, without rewriting the entire application. They could even potentially support both IDs for a time. (For example, a web endpoint might accept an integer key and then redirect to the GUID key to ensure bookmarks aren't broken.) This also becomes the motive for slowly migrating away from using the integer keys in code.
- The synchronization code might have to map integer surrogate IDs when synchronizing data with a foreign key. This is far from an intractable problem, though. You just look up the related synchronization ID and use it to find the destination database's integer surrogate key. However, it sounds like your development team is already prepared to switch foreign keys from the existing surrogate keys to the new GUID keys, anyway, so this may not be an issue at all.
Both of these problems are manageable, though, and they're reasonable trade offs to make if switching everything to GUIDs right now is too expensive.
It's also worth noting that this solution enabled exactly the query they're asking to be able to do.
This may be too late to help you now, but I think it's good info going forward.