Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Comparing GUID and uniqueidentifier Values

The globally unique identifier (GUID) data type in SQL Server is represented by the uniqueidentifier data type, which stores a 16-byte binary value. A GUID is a binary number, and its main use is as an identifier that must be unique in a network that has many computers at many sites. GUIDs can be generated by calling the Transact-SQL NEWID function, and is guaranteed to be unique throughout the world. For more information, see "Using uniqueidentifier Data" in SQL Server Books Online.

Because GUIDs values are long and obscure, they are not meaningful for users. If randomly generated GUIDs are used for key values and you insert a lot of rows, you get random I/O into your indexes, which can negatively impact performance. GUIDs are also relatively large when compared to other data types. In general we recommend using GUIDs only for very narrow scenarios for which no other data type is suitable.

Comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that are allowed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators are allowed.

Both Guid and SqlGuid have a CompareTo method for comparing different GUID values. However, System.Guid.CompareTo and SqlTypes.SqlGuid.CompareTo are implemented differently. SqlGuid implements CompareTo using SQL Server behavior, in the last six bytes of a value are most significant. Guid evaluates all 16 bytes. The following example demonstrates this behavioral difference. The first section of code displays unsorted Guid values, and the second section of code shows the sorted Guid values. The third section shows the sorted SqlGuid values. The output is displayed beneath the code listing.

private static void WorkWithGuids()
    // Create an ArrayList and fill it with Guid values.
    ArrayList guidList = new ArrayList();
    guidList.Add(new Guid("3AAAAAAA-BBBB-CCCC-DDDD-2EEEEEEEEEEE"));
    guidList.Add(new Guid("2AAAAAAA-BBBB-CCCC-DDDD-1EEEEEEEEEEE"));
    guidList.Add(new Guid("1AAAAAAA-BBBB-CCCC-DDDD-3EEEEEEEEEEE"));

    // Display the unsorted Guid values.
    Console.WriteLine("Unsorted Guids:");
    foreach (Guid guidValue in guidList)
        Console.WriteLine(" {0}", guidValue);

    // Sort the Guids.

    // Display the sorted Guid values.
    Console.WriteLine("Sorted Guids:");
    foreach (Guid guidSorted in guidList)
        Console.WriteLine(" {0}", guidSorted);

    // Create an ArrayList of SqlGuids.
    ArrayList sqlGuidList = new ArrayList();
    sqlGuidList.Add(new SqlGuid("3AAAAAAA-BBBB-CCCC-DDDD-2EEEEEEEEEEE"));
    sqlGuidList.Add(new SqlGuid("2AAAAAAA-BBBB-CCCC-DDDD-1EEEEEEEEEEE"));
    sqlGuidList.Add(new SqlGuid("1AAAAAAA-BBBB-CCCC-DDDD-3EEEEEEEEEEE"));

    // Sort the SqlGuids. The unsorted SqlGuids are in the same order 
    // as the unsorted Guid values.

    // Display the sorted SqlGuids. The sorted SqlGuid values are ordered 
    // differently than the Guid values.
    Console.WriteLine("Sorted SqlGuids:");
    foreach (SqlGuid sqlGuidValue in sqlGuidList)
        Console.WriteLine(" {0}", sqlGuidValue);

This example produces the following results.

Unsorted Guids:

Sorted Guids:

Sorted SqlGuids:
© 2015 Microsoft