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.

I would really be interested in knowing why you need this done. — rein, May 04 '09 at 22:48
If you find yourself in a situation that requires incrementing a GUID, 9 times out of 10 I would be re-examining what you are trying to do. — Mitch Wheat, May 05 '09 at 03:26
Instead of incrementing the GUID, maybe it would be simpler to just add an additional sequence column to disambiguate? — ahains, May 05 '09 at 05:49
So, they're supposed to be "random" as they're globally unique...why would you want to make the generation of a *next* GUID predictable? — Yuck, Aug 27 '11 at 14:01

score 4 · Answer 1 · answered Feb 02 '12 at 16:52

The way you want to increment Guid is not correct for SQL Server as Guid is a structure with different byte order in the byte groups, please have a look at: http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx and notice the following:

Now, when I run modified Alberto's query, I'm getting the following sequence: 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10

That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].

Here is simple function to increment a uniqueidentifier accounting for this:

create function [dbo].[IncrementGuid](@guid uniqueidentifier) 
returns uniqueidentifier 
as 
begin 
declare @guid_binary binary(16), @b03 binary(4), @b45 binary(2), @b67 binary(2), @b89 binary(2), @bAF binary(6)

select @guid_binary = @guid

select @b03 = convert(binary(4), reverse(substring(@guid_binary,1,4)))
select @b45 = convert(binary(2), reverse(substring(@guid_binary,5,2)))
select @b67 = convert(binary(2), reverse(substring(@guid_binary,7,2)))
select @b89 = convert(binary(2), substring(@guid_binary,9,2))
select @bAF = convert(binary(6), substring(@guid_binary,11,6))

if (@b03 < 'FFFFFFFF')
begin
    select @b03 = convert(binary(4), cast(@b03 as int) + 1)
end
else if (@b45 < 'FFFF')
begin
    select @b45 = convert(binary(2), cast(@b45 as int) + 1)
end
else if (@b89 < 'FFFF')
begin
    select @b89 = convert(binary(2), cast(@b89 as int) + 1)
end
else
begin
    select @bAF = convert(binary(6), cast(@bAF as bigint) + 1)
end

return convert(binary(16), reverse(convert(char(4),@b03)) + reverse(convert(char(2),@b45)) + reverse(convert(char(2),@b67)) + convert(char(2),@b89) + convert(char(6),@bAF))
end

Note that bytes 6 and 7 are not incremented as they contain the Guid version bits. But as others has pointed you really should not be doing this. In your case it might be better if you create a temp table for these Guids (with two columns: one integer as index and second one with generated Guids).

score 2 · Answer 2 · answered May 04 '09 at 21:43

2

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'

answered May 04 '09 at 21:43

a_hardin

4,991
4
32
40

1

What version of sql server; 2005 and 2008 have incrementing uniqueidentifiers as a data function - NEWSEQUENTIALID() – u07ch May 04 '09 at 21:49
I had hoped that would be my answer, too, but unfortunately it can't be used in queries. – a_hardin May 04 '09 at 22:06
if you can use clr then you can try this http://www.jorriss.net/blog/jorriss/archive/2008/04/24/unraveling-the-mysteries-of-newsequentialid.aspx – u07ch May 04 '09 at 22:14
I tried it, it is working fine – Mhd Saleh Dec 14 '22 at 10:17

score 2 · Answer 3 · edited Jul 01 '18 at 06:38

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(@binary16, 1, 8)
    ,@binaryLower8  = substring(@binary16, 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

Increment a uniqueidentifier in TSQL

3 Answers3

Linked