SQL Server NEWID()

SQL Server NEWID function is a system function that  is used to generate a random unique value of type uniqueidentifier. 





SYNTAX

NEWID()

A return type of NEWID function is uniqueidentifier.
Uniqueidentifier is a Microsoft SQL Server data type that is used to store Globally Unique Identifiers (GUIDs).
It can store 16 bytes of data.
Following is the valid format data value of type uniqueidentifier.

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Here x is a hexadecimal digit in the range 0-9 or a-f.

Lets look at an example of NEWID() in SQL Server.

Generate random unique id using NEWID Function

select newid() as uniqId

As you can see, it returns a unique value of type uniqueidentifier.

Using NewID function with variable

The following statement uses NEWID function to assign a value to a variable @uniqid of uniqueidentifier data type
DECLARE @uniqid uniqueidentifier 
SET @uniqid = NEWID() 
Select @uniqId as UniqId

Variable declaration to store the value generated by NEWID function

You can store the value generated by NEWID function in variable, for this you need to declare a local variable of type uniqueidentifier, then assigned a value to it by using SET statement as shown below.

DECLARE @uniqid uniqueidentifier ; 
SET @uniqid = '97125ECE-EE01-4F23-89B7-5C425EC53BF7'; 
SELECT @uniqid as uniqId

Convert uniqueidentifier generated by NEWID function to Integer value

You can also convert the value of data type uniqueidentifier to Integer value.
Following statement convert the uniqueidentifier generated by the NEWID function to integer value.
The random unique value of data type uniqueidentifier generated by NewID function may include negative value also.
As you can see, executing the following statement for multiple times it may generate the negative values also.
For first time it returns positive integer value, and second time it returns negative integer value.
 
SELECT CAST(CAST(NEWID() AS VARBINARY) AS INT) AS UniqId
If you want only positive value then you can use ABS function to get the positive values only.
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS UniqId_positive

NEWID function with create table statement

Following statement creates a new Demo table with a uniqueidentifier data type, and uses NEWID function to generate the default value for DemoId column when record is inserted in table.
CREATE TABLE dbo.Demo
(
DemoId uniqueidentifier NOT NULL DEFAULT NEWID(),
DemoName varchar(150)
)

INSERT INTO dbo.Demo
(DemoName) VALUES ('Demo On - Logistic Track Management')

 

As you have created a table and inserted one record in table, Lets select the record from a table to see whether a Unique Id value is generated for column DemoId or not.
You can see, unique id value is generated for column DemoId.




Also Read

NEWSEQUENTIALID() Function

 541 total views,  3 views today

3 thoughts on “SQL Server NEWID()

  1. Srikanth February 10, 2020 at 9:42 am

    Newid creates a random value and creates too much of fragmentation if used as primary key. New sequencial ID is a better option.

  2. Andres Hernandez July 31, 2020 at 3:31 am

    ¿La función nunca devuelve un valor igual a otro ya arrojado en el pasado?

    • SqlSkull July 31, 2020 at 10:03 am

      As per microsoft documentation on UniqueIdentifier, This value is always a unique globally beacuse it is generate based on network clock and CPU clock time

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.