Skip to content

Latest commit

 

History

History
79 lines (63 loc) · 4.51 KB

uniqueidentifier-transact-sql.md

File metadata and controls

79 lines (63 loc) · 4.51 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
uniqueidentifier (Transact-SQL)
uniqueidentifier (Transact-SQL)
MikeRayMSFT
mikeray
12/01/2017
sql
t-sql
reference
uniqueidentifier
uniqueidentifier_TSQL
uniqueidentifier data type
globally unique identifiers [SQL Server]
GUIDs [SQL Server]
TSQL
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=Fabric

uniqueidentifier (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

Is a 16-byte GUID.

Remarks

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

  • By using the NEWID or NEWSEQUENTIALID functions.
  • By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

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 can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

Merge replication and transactional replication with updating subscriptions use uniqueidentifier columns to guarantee that rows are uniquely identified across multiple copies of the table.

Converting uniqueidentifier Data

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. See the Examples section.

Limitations and restrictions

These tools and features do not support the uniqueidentifier data type:

Examples

The following example converts a uniqueidentifier value to a char data type.

DECLARE @myid uniqueidentifier = NEWID();  
SELECT CONVERT(CHAR(255), @myid) AS 'char';

The following example demonstrates the truncation of data when the value is too long for the data type being converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.

DECLARE @ID NVARCHAR(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';  
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;

[!INCLUDEssResult]

String                                       TruncatedValue  
-------------------------------------------- ------------------------------------  
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong    0E984725-C51C-4BF4-9960-E1C80E27ABA0  
  
(1 row(s) affected)

See also

ALTER TABLE (Transact-SQL)
CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
NEWID (Transact-SQL)
NEWSEQUENTIALID (Transact-SQL)
SET @local_variable (Transact-SQL)
Updatable Subscriptions for Transactional Replication