Skip to content

compare-guid-uniqueidentifier-values.md

Latest commit

 

History

History
52 lines (41 loc) · 3.22 KB

compare-guid-uniqueidentifier-values.md

File metadata and controls

52 lines (41 loc) · 3.22 KB
title Comparing GUID and uniqueidentifier values
description Demonstrates how to work with GUID and uniqueidentifier values in SQL Server and .NET.
ms.date 09/30/2019
dev_langs
csharp
ms.assetid aababd75-2335-43e3-ace8-4b7ae84191a8
ms.prod sql
ms.prod_service connectivity
ms.technology connectivity
ms.topic conceptual
author David-Engel
ms.author v-daenge
ms.reviewer v-kaywon

Comparing GUID and uniqueidentifier values

[!INCLUDEDriver_ADONET_Download]

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 uniqueidentifier (Transact-SQL).

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

[!code-csharpDataWorks SqlGuid#1]

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

Next steps