Best way to get PK Guid of inserted row

Question

I've read this question about getting the identity of an inserted row. My question is sort of related.

Is there a way to get the guid for an inserted row? The table I am working with has a guid as the primary key (defaulted to newid), and I would like to retrieve that guid after inserting the row.

Is there anything like @@IDENTITY, IDENT_CURRENT or SCOPE_IDENTITY for Guids?

Schwa

Answer 1

You can use the OUTPUT functionality to return the default values back into a parameter.

CREATE TABLE MyTable
(
    MyPK UNIQUEIDENTIFIER DEFAULT NEWID(),
    MyColumn1 NVARCHAR(100),
    MyColumn2 NVARCHAR(100)
)

DECLARE @myNewPKTable TABLE (myNewPK UNIQUEIDENTIFIER)

INSERT INTO 
    MyTable
(
    MyColumn1,
    MyColumn2
)
OUTPUT INSERTED.MyPK INTO @myNewPKTable
VALUES
(
    'MyValue1',
    'MyValue2'
)

SELECT * FROM @myNewPKTable

I have to say though, be careful using a unique identifier as a primary key. Indexing on a GUID is extremely poor performance as any newly generated guids will have to be inserted into the middle of an index and rrarely just added on the end. There is new functionality in SQL2005 for NewSequentialId(). If obscurity is not required with your Guids then its a possible alternative.

Fast

Hi, thanks for the advice. This code returns an error "Must declare the table variable "@myNewPK"". It seems you can only output into a table. I have got it working by declaring a temporary table and then selecting the id from the table. Thanks for your help, and the advice on the poor performance of Guids. – Schwa

Sorry, I hadn't tested it, was just throwing it together from what I remembered. Have now updated the answer with it returning a table variable. – Fast

Not sure why you have trouble simply selecting the INSERTED values without going into a temp table first... "INSERT INTO Table (Fields) OUTPUT INSERTED.* VALUES (FieldValues)" works perfectly for me. SQL Server 10.0.1600. – Basilius

I think @MichaelBray has a point; same point as seen here ("No need for a separate SELECT..."). Please also note the difference between getting a single GUID for one inserted row (ExecuteScalar), or multiple GUID's for multiple rows inserted at the same time (ExecuteReader), as seen here – Aureomycin

Answer 2

Another cleaner approach, if inserting a single row

CREATE TABLE MyTable
(
    MyPK UNIQUEIDENTIFIER DEFAULT NEWID(),
    MyColumn1 NVARCHAR(100),
    MyColumn2 NVARCHAR(100)
)

DECLARE @MyID UNIQUEIDENTIFIER;
SET @MyID = NEWID();

INSERT INTO 
    MyTable
(
    MyPK
    MyColumn1,
    MyColumn2
)
VALUES
(
    @MyID,
    'MyValue1',
    'MyValue2'
)

SELECT @MyID;

Jorum