How is a guid actually stored and sorted/compared in SQL Server?

Question

Say I have this guid:

{2A87E3E2-2B6A-4149-9F5A-1B76092843D9}

Does it actually store this an an alpha numeric in the database? (I don't think so cause it has to fit in 16 bytes.)

If it does not, then how is it stored? (My guess is as a hex number, but I am not sure.)

How can you tell if one GUID is greater than another? (Which you may need to know for indexing purposes.) Do you read the GUID just like a hex number (for comparison)?

Quinnquinol

GUIDs are just a bunch of bits. We turn represent them as hexadecimal for our benefit (just like ASCII letters and numbers are really just bits, too). – Taipan

@Michael, I guess I am wondering how it get serialized to bits. Sure the OS just runs on bits. But I think there is a logical level in between the GUID I see and the 1s and 0s. – Quinnquinol

not really. Sure, the digits are printed in a fancy format but really you can do this on an envelope. Use this site to convert the letters into groups of 4 bits, or reverse electronics-tutorials.ws/binary/bin_3.html – Taipan

I don't know, but I would bet the sorting is simply by the binary representation (e.g. 00 < 01 < 10 < 11 ...). Unless you have uncommon culture settings, this would equate to a simple alphabetical sort. – Taipan

@Michael Haren: GUIDs have a defined sort logic: it isn't what you think... – Jyoti

Answer 1

A GUID is stored as binary(16) internally. "Using uniqueidentifier Data" on MSDN tells you this. The { } and - are not part of the value.

GUIDs can be "sorted" and have greater/lesser comparisons: see the canonical "How are GUIDs sorted by SQL Server?".

Note: this means they don't sort as binary(16) (unless you CAST I suppose...)

I can't see why you'd want this in real life (not indexing, I mean real world): about the only use for the "sorting" potential of NEWID is for the random rows tricks of ORDER BY NEWID()

You can have "ascending" GUIDs (based on the article above) with NEWSEQUENTIALID. However, this "sorting" doesn't continue after a Windows restart = pointless IMO.

Jyoti

Fantastic answer. Just what I was looking for. Thanks! And I agree, the reboot limitation on NewSequentialId makes it far less useful... – Quinnquinol

It's a bad function name. The GUIDs aren't sequential, rather they are monotonically increasing. What it does is create a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. It doesn't say there can't be gaps in between. But the important part is that the Guid are "ordered" in a way that do not cause page splits (if the Guid is used in a index) and this is what happens when using the new sequential guid. – Nowlin

The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer. – Nowlin

Answer 2

Necromancing.
The GUID is stored as byte-array, that is to say binary(16).
You could also store it as UInt128, or two bigints.

As to how a GUID is sorted:
The code speaks for itselfs, the magical parts are

System.Guid g
g.ToByteArray();
int[] m_byteOrder = new int[16] // 16 Bytes = 128 Bit 
    {10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3};


public int Compare(Guid x, Guid y)
{
    byte byte1, byte2;

    //Swap to the correct order to be compared
    for (int i = 0; i < NUM_BYTES_IN_GUID; i++)
    {
        byte1 = x.ToByteArray()[m_byteOrder[i]];
        byte2 = y.ToByteArray()[m_byteOrder[i]];
        if (byte1 != byte2)
            return (byte1 < byte2) ? (int)EComparison.LT : (int)EComparison.GT;
    } // Next i 

    return (int)EComparison.EQ;
}

Full code:

namespace BlueMine.Data
{


    public class SqlGuid
        : System.IComparable
        , System.IComparable<SqlGuid>
        , System.Collections.Generic.IComparer<SqlGuid>
        , System.IEquatable<SqlGuid>
    {
        private const int NUM_BYTES_IN_GUID = 16;

        // Comparison orders.
        private static readonly int[] m_byteOrder = new int[16] // 16 Bytes = 128 Bit 
        {10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3};

        private byte[] m_bytes; // the SqlGuid is null if m_value is null


        public SqlGuid(byte[] guidBytes)
        {
            if (guidBytes == null || guidBytes.Length != NUM_BYTES_IN_GUID)
                throw new System.ArgumentException("Invalid array size");

            m_bytes = new byte[NUM_BYTES_IN_GUID];
            guidBytes.CopyTo(m_bytes, 0);
        }


        public SqlGuid(System.Guid g)
        {
            m_bytes = g.ToByteArray();
        }


        public byte[] ToByteArray()
        {
            byte[] ret = new byte[NUM_BYTES_IN_GUID];
            m_bytes.CopyTo(ret, 0);
            return ret;
        }

        int CompareTo(object obj)
        {
            if (obj == null)
                return 1; // https://msdn.microsoft.com/en-us/library/system.icomparable.compareto(v=vs.110).aspx

            System.Type t = obj.GetType();

            if (object.ReferenceEquals(t, typeof(System.DBNull)))
                return 1;

            if (object.ReferenceEquals(t, typeof(SqlGuid)))
            {
                SqlGuid ui = (SqlGuid)obj;
                return this.Compare(this, ui);
            } // End if (object.ReferenceEquals(t, typeof(UInt128)))

            return 1;
        } // End Function CompareTo(object obj)


        int System.IComparable.CompareTo(object obj)
        {
            return this.CompareTo(obj);
        }


        int CompareTo(SqlGuid other)
        {
            return this.Compare(this, other);
        }


        int System.IComparable<SqlGuid>.CompareTo(SqlGuid other)
        {
            return this.Compare(this, other);
        }


        enum EComparison : int
        {
            LT = -1, // itemA precedes itemB in the sort order.
            EQ = 0, // itemA occurs in the same position as itemB in the sort order.
            GT = 1 // itemA follows itemB in the sort order.
        }


        public int Compare(SqlGuid x, SqlGuid y)
        {
            byte byte1, byte2;

            //Swap to the correct order to be compared
            for (int i = 0; i < NUM_BYTES_IN_GUID; i++)
            {
                byte1 = x.m_bytes[m_byteOrder[i]];
                byte2 = y.m_bytes[m_byteOrder[i]];
                if (byte1 != byte2)
                    return (byte1 < byte2) ? (int)EComparison.LT : (int)EComparison.GT;
            } // Next i 

            return (int)EComparison.EQ;
        }


        int System.Collections.Generic.IComparer<SqlGuid>.Compare(SqlGuid x, SqlGuid y)
        {
            return this.Compare(x, y);
        }


        public bool Equals(SqlGuid other)
        {
            return Compare(this, other) == 0;
        }


        bool System.IEquatable<SqlGuid>.Equals(SqlGuid other)
        {
            return this.Equals(other);
        }


    }


}

Nowlin