Michael Cole

2015-08-11 04:07:19 UTC

Brief Synopsis: -

I have a table with a GUID as PK (created via NewId())

I also have a table to indicate links between records in this table,

with a dual PK of GUIDS (for the two records that connect to each

other)

Putting a constrain on these two fields will limit it to only one

combination of the two fields, i.e., a link of A to B, but I also need

to ensure that the link is not duplicated as B to A - the link is

non-directional.

My idea was to include a calculated field of the two GUIDs XORed

together, and place a constraint on this calculated field. Can anyone

see any issues with this idea?

For reference, the function would be: -

CREATE FUNCTION GUIDXor ( @guid1 UNIQUEIDENTIFIER, @guid2

UNIQUEIDENTIFIER)

RETURNS UNIQUEIDENTIFIER

AS

BEGIN

-- variables

DECLARE @vb1 BINARY(16), @vb2 BINARY(16), @lo BINARY(8), @hi BINARY(8)

-- split every 8 bytes into a binary(8), which is a bigint, the

largest size usable with XOR

SELECT @vb1 = @guid1, @vb2 = @guid2

-- xor the high and low parts separately

SELECT @hi = CONVERT(binary(8), SUBSTRING(@vb1,1,8)) ^ CONVERT(bigint,

SUBSTRING(@vb2,1,8))

SELECT @lo = CONVERT(binary(8), SUBSTRING(@vb1,9,8)) ^ CONVERT(bigint,

SUBSTRING(@vb2,9,8))

RETURN CONVERT(UNIQUEIDENTIFIER, @hi + @lo)

END

GO

SELECT dbo.GUIDXor('96B4316D-1EA7-4CA3-8D50-FEE8047C1329',

'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF')

SELECT dbo.GUIDXor('96B4316D-1EA7-4CA3-8D50-FEE8047C1329',

'00000000-0000-0000-0000-000000000000')

I have a table with a GUID as PK (created via NewId())

I also have a table to indicate links between records in this table,

with a dual PK of GUIDS (for the two records that connect to each

other)

Putting a constrain on these two fields will limit it to only one

combination of the two fields, i.e., a link of A to B, but I also need

to ensure that the link is not duplicated as B to A - the link is

non-directional.

My idea was to include a calculated field of the two GUIDs XORed

together, and place a constraint on this calculated field. Can anyone

see any issues with this idea?

For reference, the function would be: -

CREATE FUNCTION GUIDXor ( @guid1 UNIQUEIDENTIFIER, @guid2

UNIQUEIDENTIFIER)

RETURNS UNIQUEIDENTIFIER

AS

BEGIN

-- variables

DECLARE @vb1 BINARY(16), @vb2 BINARY(16), @lo BINARY(8), @hi BINARY(8)

-- split every 8 bytes into a binary(8), which is a bigint, the

largest size usable with XOR

SELECT @vb1 = @guid1, @vb2 = @guid2

-- xor the high and low parts separately

SELECT @hi = CONVERT(binary(8), SUBSTRING(@vb1,1,8)) ^ CONVERT(bigint,

SUBSTRING(@vb2,1,8))

SELECT @lo = CONVERT(binary(8), SUBSTRING(@vb1,9,8)) ^ CONVERT(bigint,

SUBSTRING(@vb2,9,8))

RETURN CONVERT(UNIQUEIDENTIFIER, @hi + @lo)

END

GO

SELECT dbo.GUIDXor('96B4316D-1EA7-4CA3-8D50-FEE8047C1329',

'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF')

SELECT dbo.GUIDXor('96B4316D-1EA7-4CA3-8D50-FEE8047C1329',

'00000000-0000-0000-0000-000000000000')

--

Michael Cole

Michael Cole