Comparing GUID and uniqueidentifier Values (ADO.NET)
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.
Working with SqlGuid Values
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.
Comparing GUID Values
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 which only the last 6 bytes of a value are evaluated. 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 Sub WorkWithGuids()
' Create an ArrayList and fill it with Guid values.
Dim guidList As 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:")
For Each guidValue As Guid In guidList
Console.WriteLine("{0}", guidValue)
Next
Console.WriteLine()
' Sort the Guids.
guidList.Sort()
' Display the sorted Guid values.
Console.WriteLine("Sorted Guids:")
For Each guidSorted As Guid In guidList
Console.WriteLine("{0}", guidSorted)
Next
Console.WriteLine()
' Create an ArrayList of SqlGuids.
Dim sqlGuidList As 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.
sqlGuidList.Sort()
' Display the sorted SqlGuids. The sorted SqlGuid values are
' ordered differently than the Guid values.
Console.WriteLine("Sorted SqlGuids:")
For Each sqlGuidValue As SqlGuid In sqlGuidList
Console.WriteLine("{0}", sqlGuidValue)
Next
End Sub
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);
}
Console.WriteLine("");
// Sort the Guids.
guidList.Sort();
// Display the sorted Guid values.
Console.WriteLine("Sorted Guids:");
foreach (Guid guidSorted in guidList)
{
Console.WriteLine(" {0}", guidSorted);
}
Console.WriteLine("");
// 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.
sqlGuidList.Sort();
// 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:
3aaaaaaa-bbbb-cccc-dddd-2eeeeeeeeeee
2aaaaaaa-bbbb-cccc-dddd-1eeeeeeeeeee
1aaaaaaa-bbbb-cccc-dddd-3eeeeeeeeeee
Sorted Guids:
1aaaaaaa-bbbb-cccc-dddd-3eeeeeeeeeee
2aaaaaaa-bbbb-cccc-dddd-1eeeeeeeeeee
3aaaaaaa-bbbb-cccc-dddd-2eeeeeeeeeee
Sorted SqlGuids:
2aaaaaaa-bbbb-cccc-dddd-1eeeeeeeeeee
3aaaaaaa-bbbb-cccc-dddd-2eeeeeeeeeee
1aaaaaaa-bbbb-cccc-dddd-3eeeeeeeeeee