Note: This is not about database design nor about the general use of a GUID. This is about deterministically create such GUID's for test data, on a Microsoft SQL server.
We are migrating our database away from integer identifiers to the uniqueidentifier data type.
For test purposes we want to migrate our test data sets to known GUID values, deterministically based on our former integer values
UPDATE Unit
SET UnitGuid = NEWID(UnitId)
Obviously this does not work right away. How to use the UnitId to create a deterministic GUID?
Stop thinking about the problem from a "string" perspective. an int is made up of 4 bytes. A uniqueidentifier is made up of 16 bytes. you can easily take 12 fixed bytes and append the four bytes from an int to the end of those, and get a solution that works for all int values:
declare @Unit table
(
UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10),
IntegerId int
)
-- Add *3* data rows
INSERT INTO @Unit(Characters, IntegerId) VALUES ('abc', 1111),('def', 2222),('ghi',-17)
-- Deterministically creates a uniqueidentifier value out of an integer value.
DECLARE @GuidPrefix binary(12) = 0xefbeadde0000000000000000
UPDATE @Unit
SET UniqueColumn = CONVERT(uniqueidentifier,@GuidPrefix + CONVERT(binary(4),IntegerId))
-- Check the result
SELECT * FROM @Unit
Result:
UniqueColumn Characters IntegerId
------------------------------------ ---------- -----------
DEADBEEF-0000-0000-0000-000000000457 abc 1111
DEADBEEF-0000-0000-0000-0000000008AE def 2222
DEADBEEF-0000-0000-0000-0000FFFFFFEF ghi -17
(For various reasons, we have to provide the first four bytes in a different order than the one that is used by default when displaying a uniqueidentifier as a string, which is why if we want to display DEADBEEF, we had to start our binary as efbeadde)
Also, of course, insert usual warnings that if you're creating guids/uniqueidentifiers but not using one of the prescribed methods for generating them, then you cannot assume any of the usual guarantees about uniqueness.