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?

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.

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;