Skip to main content

UUID datatype

The data type UUID allowes storage of valid Universally Unique IDentifiers as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards. It can be used as a global unique 128-bit identifier.

UUIDs are generated by an algorithm that ensures an extreme low probability that two calls to the same create function will ever produce the same value. They are often used in distributed (web-based) systems for this reason.

A UUID is written as a sequence of lower-case hexadecimal digits, in several groups optionally separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits. An example of a UUID in this standard form is:

select sys.uuid() as uuid;
+--------------------------------------+ 
| uuid                                 | 
+======================================+
| 65950c76-a2f6-4543-660a-b849cf5f2453 |
+--------------------------------------+

The system function sys.uuid() generates a new random uuid and returns the uuid value.

The system function sys.isauuid(string) checks whether the string value satisfies the grammatical UUID structure and returns a boolean value true or false, or null when the input is null. A valid string should be 36 or 32 characters long, consists of hexadecimals characters and when 36 characters long have hyphens at locations 9, 14, 19 and 24.

 

You can use cast() or convert() function or uuid prefix to a quoted string literal to convert a valid uuid string to a uuid type.

select cast( '26d7a80b-7538-4682-a49a-9d0f9676b765' as uuid) as uuid_val;
select convert('83886744-d558-4e41-a361-a40b2765455b', uuid) as uuid_val;
select     uuid'AC6E4E8C-81B5-41B5-82DE-9C837C23B40A' as uuid_val;

See also UUID functions.