SQL Server Java Compatible GUID / UUID (Big Endian UniqueIdentifier)

Question

I am trying to convert a 128 bit binary to a uniqueidentifier in sql that is the same as in .net and java.

I know java uses big endians, so I would like to make that the base.

I can get the correct endianess in .net, but am really struggling with it in SQL Server.

Java:

        byte[] bytesOfMessage = "google.com".getBytes("UTF-8");

        MessageDigest md = MessageDigest.getInstance("MD5");
        byte[] md5 = md.digest(bytesOfMessage);
        ByteBuffer bb = ByteBuffer.wrap(md5);
        LongBuffer ig = bb.asLongBuffer();
        return new UUID(ig.get(0), ig.get(1));

returns 1d5920f4-b44b-27a8-02bd-77c4f0536f5a

.Net

    System.Security.Cryptography.MD5 c = System.Security.Cryptography.MD5.Create();
    byte[] b = c.ComputeHash(Encoding.UTF8.GetBytes("google.com"));
    int z = System.Net.IPAddress.HostToNetworkOrder(BitConverter.ToInt32(b, 0));
    short y = System.Net.IPAddress.HostToNetworkOrder(BitConverter.ToInt16(b, 4));
    short x = System.Net.IPAddress.HostToNetworkOrder(BitConverter.ToInt16(b, 6));
    Guid g = new Guid(z, y, x, b.Skip(8).ToArray());
    return g;

returns 1d5920f4-b44b-27a8-02bd-77c4f0536f5a

SQL

DECLARE @s VARCHAR(MAX) = 'google.com' --'goolge.com'

DECLARE @md5 BINARY(16) = HASHBYTES
        (
            'MD5',
            @s
        )

DECLARE @a BINARY(4) = 
        CONVERT
        (
            BINARY(4),
            REVERSE
            (
                CONVERT
                (
                    BINARY(4),
                    LEFT(@md5, 4)
                )
            )
        )

DECLARE @b BINARY(2) = 
        CONVERT
        (
            BINARY(2),
            REVERSE
            (
                CONVERT
                (
                    BINARY(2),
                    RIGHT(@md5, 12)
                )
            )
        )

DECLARE @c BINARY(2) = 
        CONVERT
        (
            BINARY(2),
            REVERSE
            (
                CONVERT
                (
                    BINARY(2),
                    RIGHT(@md5, 10)
                )
            )
        )

DECLARE @d BINARY(8) = 
        CONVERT
        (
            BINARY(8),
            RIGHT(@md5, 8)
        )

SELECT  
    CONVERT
    (
        UNIQUEIDENTIFIER,
        @a + @b + @c + @d
    )

returns D86B5A7F-7A25-4895-A6D0-63BA3A706627

I am able to get all three to produce the same value when converting to an int64, but the GUID is baffling me.

Original Issue

Original Answer

Did you look into writing a .Net CLR function in SQL Server? — Michael Harmon, Apr 15 '13 at 17:37
Not to be picky, but why does your SQL show: `DECLARE @s VARCHAR(MAX) = 'goolge.com'` instead of `DECLARE @s VARCHAR(MAX) = 'google.com'`? — Sam Goldberg, Apr 15 '13 at 17:39
One more question: are you sure that using the VARCHAR datatype encodes the string to the same bytes as the UTF-8 encoding you are using in Java and .NET? Perhaps you can first check intermediate result that the starting string bytes are the same for .NET and sql server (before you create MD5 hash). — Sam Goldberg, Apr 15 '13 at 18:01
Good catch Sam. I checked everything, except the obvious. All three examples now return the same thing. Hash away! — Eulalie367, Apr 16 '13 at 20:05

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

SQL Server does not support UTF-8 encoding. See Description of storing UTF-8 data in SQL Server. Use suggestion from Michael Harmon to add your .NET function to SQLServer to do the conversion. See How to encode... for instructions on how to add your .NET function to SQLServer.

Alternatively, don't specify UTF-8 in your Java and .NET code. I believe SQL Server will use same 256 bit encoding for varchar as does Java and .NET. (But not totally sure of this.)

edited May 23 '17 at 12:12

Community

1
1

answered Apr 15 '13 at 18:16

Sam Goldberg

6,711
8
52
85

1

UTF-8 is the default for my instance of SQL Server. If your instance is different you would need to amend the java/.net code accordingly. – Eulalie367 Apr 16 '13 at 20:07

score 1 · Accepted Answer · answered Apr 16 '13 at 15:27

1

If you correct the spelling of google in your SQL example (it's goolge in your post), you get the right result.

answered Apr 16 '13 at 15:27

muhmud

4,474
2
15
22

SQL Server Java Compatible GUID / UUID (Big Endian UniqueIdentifier)

2 Answers2