Deterministic GUID generation in T-SQL

1
votes

I have to prepare a script that selects data from a couple of tables and inserts new rows in the table in which some sort of many-to-many relationship is stored.

So, basically, I have two tables with uniqueidentifier field as a primary key and another table that stores two foreign keys from these tables and some additional data. They primary key of this table is the uniqueidentifier as well.

INSERT ResultTable ([primaryKey],[foreignA], [foreignB])
    SELECT  newid(), /* <- Can't have it! */
            @foreignKeyA,
            fb.[primaryKey]
    FROM foreignTableB as fb

The problem is that I can't use newid() to generate GUIDs for my generated data. One of the requirements is that the script produces identical results (primary keys included) when it's ran on identical tables. So I have to come up with a script that will insert new rows with uniqueidentifier value that is generated based on two other uniqueidentifier values.

Now, I don't know of any functions that provide mapping from one GUID to another. Probably there aren't any. But I still hope to hear some proposals and advices.

Also:

  • We can assume that any pair of foreign keys is unique throughout the table
  • I can't change the type of primary key fields in any of the tables mentioned
  • It's mostly SQL Server 2005/2008 but some might run SQL Server 2000

Thanks in advance.

2
tsqlguiduniqueidentifier
Sounds like this would be much the same as taking integer #1 and integer #2, and munging them together somehow such that integer #3 can be used as a primary key. I am not aware of any way to do this with a 100% certainty of any two pairs of integers--or GUIDs--not producing the same "output" integer. (Hashing algorithms might get you close, if you can find one that generates GUID values) – Philip Kelley
@Philip Kelley: I'm pretty sure I can do something like this by converting both GUIDs to strings and manipulating with characters (reordering them) and then converting the resulting string back to uniqueidentifier. I would probably end up doing something like this (even though it's incredibly messy and most likely a bad idea), but I decided I don't have to – Dyppl

2 Answers

2
votes

Your best answer here is to generate a script that is a list of insert statements. This script can be generated from another script that generates the dynamic SQL string. You will probably do this iteratively which calls your guid function each loop.

This way at the end you will have a list of inserts that can be run multiple times with the same results with guids.

But one of the dimensions of generating guids is time so you will never be able to reproduce the guid (which is kind of the point really)

0
votes

You could first use CHECKSUM to get integer value from multiple columns and then convert it back to GUID:

CREATE TABLE ResultTable ([primaryKey] UNIQUEIDENTIFIER,[foreignA] INT, [foreignB] INT);
CREATE TABLE foreignTableB(primaryKey INT);
INSERT INTO foreignTableB(primaryKey) VALUES (1),(2),(3),(4),(5),(100),(1000);


DECLARE @foreignKeyA INT = 1;

INSERT ResultTable ([primaryKey],[foreignA], [foreignB])
SELECT CAST(CAST(CHECKSUM(@foreignKeyA, fb.primaryKey) AS VARBINARY(4)) AS UNIQUEIDENTIFIER)
      , @foreignKeyA, fb.[primaryKey]
FROM foreignTableB as fb;

SELECT * FROM ResultTable;

db<>fiddle demo