Home » SQL Server NEWID()

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 we 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

First we will declare a local variable of type uniqueidentifier , then assigned a value to it by using SET statement.
DECLARE @uniqid uniqueidentifier ; 
SET @uniqid = '97125ECE-EE01-4F23-89B7-5C425EC53BF7'; 
SELECT @uniqid as uniqId

Convert Uniqueidentifier generated by NEWID() function to Integer value

Following statement convert the uniqueidentifier generated by the NEWID() to integer value.
The random unqiue value may include negative value also.
 
SELECT CAST(CAST(NEWID() AS VARBINARY) AS INT) AS UniqId
As we see , First time it returns positve integer value , and second time it returns Negative integer value.
If want only positive value then we 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.

CREATE TABLE Demo
(
DemoId uniqueidentifier NOT NULL DEFAULT newid(),
DemoName varchar(50)
)

INSERT Demo
(DemoName) VALUES (‘Demo on -Inventory module access rights‘)

 

As we have created a table and inserted one record in table, Lets select record from a table to see whether a Unique Id value generated or not for column DemoId.
We can see , unqiue id value is generated for column demoId.




3 thoughts on “SQL Server NEWID()”

Leave a Reply

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