Something I looked-up just to confirm what I already knew– there isn’t a way to automagically get the value of a GUID after inserting into SQL Server by using something similar to SELECT @@identity as you would with normal identity column. Instead you can do this:
Now, unlike an IDENTITY column, a uniqueidentifier column doesn’t automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')
As a sidenote, I’ve not worked in SQL Server nearly as much the past couple of years as I have previously because our primary database platform is something different at work, so there are a few things I’m rusty with for sure.
By PaulH February 2, 2005 - 6:31 pm
hang a trigger off that table to return that UID column, the NewID() function might be better used as the default vaue in that table’s definition. less “codey” anyways.
By Brandon Harper February 2, 2005 - 6:37 pm
Paul,
As I need the GUID for a couple of more queries in this particular Stored Proc, I don’t mind creating the GUID first and using them on the subsequent inserts as well rather than doing a Stored Proc AND a Trigger. Good suggestion though.
By Laurence Middleton March 7, 2005 - 5:36 am
Hi, Brandon.
I develop CF apps and our shop has wrestled with the return-the-new-id thing in a few different contexts. Lately we’ve switched from GUID / UUID primary keys in our SQL Server db’s to non-identity integers, but that’s another story…
Below is a sample stored procedure of the sort we have used to insert a record and retrieve its primary key in one operation.
CREATE PROCEDURE insert_and_get_newGroup
@GroupTypeID uniqueidentifier,
@GroupName nvarchar(50),
@GroupID uniqueidentifier output
AS
SET @GroupID = newid()
BEGIN TRANSACTION NewGroup
insert into Groups
(GroupID, FKGroupTypeID, GroupName)
values
(@GroupID, @GroupTypeID, @GroupName)
COMMIT TRANSACTION NewGroup
GO
The other thing we have most often done when we need a GUID for multiple queries in a transaction (e.g. insert a record and then use its PK as foreign key to update a bunch of related tables) is generate it in CF rather than the database. CF’s UUID type is accepted by SQL Server as a GUID if you insert an extra hyphen and pass it in as a string.
By Carros DF May 1, 2008 - 2:43 pm
We are rewriteing our database to use NEWID() for a security question. Using GUID is impossible to any user guess any information. This is important for a large multi-user system that use web as platform.
By Mike Bishop June 23, 2009 - 11:15 am
If you have a DEFAULT of NEWID() … or the newer NEWSEQUENTIALID() … on the unique identifier column, then you can use the OUTPUT keyword of the INSERT statement – For example:
INSERT INTO dbo.Products ( …. )
OUTPUT inserted.ProductID VALUES ( … )
By Brandon Wiesner July 17, 2009 - 12:01 pm
Unfortunately the OUTPUT statement doesn’t work if your tables are replicated (because of the triggers on those tables).
By Brent Coppock January 10, 2011 - 11:20 am
Using newsequentialid instead of newid is the way to go. Huge performance gains for INSERTS, UPDATES, and DELETEs because the index does not become fragmented so quickly as it does with newid.
You might want to research this on your own to figure it out, feel free to contact me (the blogger who has my email) to get more info.