Skip to content


Returning the Value of a Uniqueidentifier after an Insert in SQL Server

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.

Posted in ColdFusion, Culture, Tips, Hacks, & Tricks.

6 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. Mike Bishop said

    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 ( … )

  6. Unfortunately the OUTPUT statement doesn’t work if your tables are replicated (because of the triggers on those tables).

Some HTML is OK

(required)

(required, but never shared)

or, reply to this post via trackback.