Increment a uniqueidentifier in TSQL

Question

I am looking for a way to increment a uniqueidentifier by 1 in TSQL. For example, if the id is A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9E, I'd like to be able to select A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9F.

@rein It's for a data import. We have an intermediate table with IDs that we're generating records from, and we join on those IDs later in the import. Unfortunately, now some of those records generate a couple of records in the next table, so we need a new id that is reproducible.

Answer 1

Here is one way I've come up with, but I'm hoping there is a better way.

LEFT([ID], 19) + RIGHT(CONVERT(uniqueidentifier, CONVERT(binary(16), CONVERT(binary(16), [ID]) + CONVERT(bigint, 1))), 17) AS 'MyNewID'

Answer 2

This is dead simple. A guid easily converts to a byte array:

Guid startingId = new Guid();
Byte[] b = startingId.ToByteArray();
b[15] += 1;

Guid updatedId = new Guid(b);

If you are trying to get a table to use sequential guids when inserting new records, you can use the NEWSEQUENTIALID() function which will generate guids... sequentially.

Answer 3

You can do this approach, but I'm not accounting for the case of overflowing lower 8 bytes.

declare @guid uniqueidentifier, @binaryUpper8 binary(8), @binaryLower8 binary(8), @binary16 binary(16), @bigint bigint
set @guid = 'A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9E'
set @binary16 = cast(@guid as binary(16))

--harvest lower 8 bytes
select @binaryUpper8= substring(0xAD60BCA6D9A4F446A7D398B2A7237A9E, 1, 8)
    ,@binaryLower8  = substring(0xAD60BCA6D9A4F446A7D398B2A7237A9E, 9, 8)
set @bigint = cast(@binaryLower8 as bigint)

--increment
set @bigint = @bigint + 1

--convert back
set @binaryLower8 = cast(@bigint as binary(8))
set @binary16 = @binaryUpper8 + @binaryLower8
set @guid = cast(@binary16 as uniqueidentifier)
select @guid