Home | Tools | Database | Design | General | Others | Scripting | Web | Windows | All
DailyCoding > Database

Generate New Guid (uniqueidentifier) in SQL Server
About how to generate a random unique identifier in using sql query in SQL server
Author admin on Jun 19, 2008 27 Comments
Rate it    (Rated 3 by 202 people)
215,655 Views

If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function.

Example

SELECT NEWID()
GO
-- This will return a new random uniqueidentifier e.g.
E75B92A3-3299-4407-A913-C5CA196B3CAB

To select this Guid in in a variable

--assign uniqueidentifier in a variable
DECLARE @EmployeeID uniqueidentifier
SET @EmployeeID = NEWID()

You can directly use this with INSERT statement to insert new row in table.

-- Inserting data in Employees table.
INSERT INTO Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID(), 'John Kris', '99-99999')
Data | SQL
 

Discussion
shahzeb On Oct 14, 2008 10:35 PM
nice example...
Sachin Gaur On Dec 23, 2008 09:14 PM
That's really nice. But can we get the empty Guid in the SEELCT statement?
Randheer On Jan 20, 2009 11:02 PM
Here the system generates the unique identity itself.
But if I want to generate my own unique identifier using some other columns. Then how can I do it? Please tell the answer.
Name On Feb 18, 2009 07:32 AM
super
Reddymade On Jul 16, 2009 08:57 AM
How can I gernerate a GUID on existing records on a data table?
Alfonso Paredes On Aug 8, 2009 10:11 AM
Reddymade

you can do something like the following

update people
set personId=NEWID()
CM On Sep 24, 2009 03:35 PM
Above didn't work for me so I used a derived table to create an 8 character default password within an existing table.

UPDATE Customers
SET custPword = newPass.newPass
FROM (SELECT Left( NEWID(),8) AS newPass) AS newPass
Pooja On Oct 21, 2009 10:59 AM
That's a good example, compact and perfect.
Rowena On Oct 12, 2010 01:56 AM
Tnx
Fayssal El Moufatich On Feb 22, 2011 03:47 AM
For performance reasons and when possible, you might want to use NEWSEQUENTIALID() instead of NEWID()
nidhi On Mar 16, 2011 10:11 PM
Thanks for this eg, its really works for me ...
hich On Jul 8, 2011 09:40 PM
i have to integrate sql server databases from different computers more than 30.
does guid make collision ?
plz help me
hich On Jul 8, 2011 09:40 PM
i have to integrate sql server databases from different computers more than 30.
does guid make collision ?
plz help me
Ramesh Soni On Jul 12, 2011 06:30 AM
@hich - Guid will never colloid even if you have 30K different computers :)
Tiger On Jul 21, 2011 06:34 AM
@Ramesh...

never say never ;-)
GUID On Aug 26, 2011 12:45 AM
@Ramesh : Actually Guid can collide but probability of that is next to impossible :)

they generate somewhere around 6 billion (not sure but a very high number) different combination. So they can collide.
jezzzzz On Sep 25, 2011 11:02 AM
nice! this is all i ever needed! ^_^ thnks!
stuff On Jan 20, 2012 07:17 AM
There are 122 random bits (128 - 2 for variant - 4 for version) so this calculates to 2^122 or 5,316,911,983,139,663,491,615,228,241,121,400,000 possible


I'm not sure where you got your number... 6 billion... this is exponentially more than that. The chance of them colliding is virtually zero across thousands of machines. You are VERY wrong 'GUID'
aisha On Jun 20, 2012 11:15 PM
thanks heaps..
Perumal B On Jun 26, 2012 09:38 AM
Thanks. This is very good.
chaos On Jul 13, 2012 11:22 AM
@stuff -

Whether it's 6 billion or 2^122 or 10^10^100 combinations, there's still a chance of collision... and THAT was GUID's point.

The chance may be nominally zero, but definitely not zero.
Alan On Aug 7, 2012 07:20 AM
Thanks!
TopVisitedSites On Aug 14, 2012 09:59 PM
nice. It's working for me.
Kevin Murphy On Sep 17, 2012 11:18 PM
My question is, if I want to generate the new guid on the basis of the user's last name, first initial and 4 numerical numbers how would this statment be written?
Sagar T On Feb 14, 2013 09:31 AM
How do I generate a GUID without hyphens(-), and specific number of characters ?
ghfhfg On Mar 14, 2013 05:59 AM
Midani On Apr 2, 2013 03:05 PM
Hello mine does not work:

SELECT NEWID()
GO
/******This will return a new random unique identifier, e.g., ******/
/******E75B92A3-3299-4407-A913-C5CA196B3CAB ******/

/******assign uniqueidentifier in a variable ******/

DECLARE @MessageID uniqueidentifier

SET @MessageID = NEWID()


/****** Inserting data in map_Missing_ADT table. ******/

INSERT INTO [DEPTH].[correction].[map_Missing_ADT]
(MapID, TypeID, MessageID, Value, Description)
VALUES
('5806D9F7-D836-456B-92E1-20AFC9F9358A', 'IC', (NEWID(), 'D14', 'XYZ COMPANY')

Leave a Comment
Name
Email Address
Web Site