SQL Server - SCOPE_IDENTITY() for GUIDs?

Question

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server?

I don't want to create the GUID first and save as a variable as we're using sequential GUIDs as our primary keys.

Any idea on what the best way to retrieve the last inserted GUID primary key.

Thanks in advance!

Answer 1

Unfortunately I don’t think there is a way to get the GUID back.
Would need to do something like this:

Declare @id uniqueidentifier
Select @id = NewID()

INSERT into tablename (id, somename) VALUES (@id, 'somebody')

Select @id as myid

Answer 2

You are forced to use NewID(), there shouldn't be an issue with that, unless you also made your GUID the clustering key as well as the primary key, in which case I would skip the clustering key over to an identity field and leave the primary on a NC index.

Answer 3

you want to use NEWID()

    declare @id uniqueidentifier
    set @id  = NEWID()
    INSERT INTO [dbo].[tbl1]
           ([id])
     VALUES
           (@id)

    select @id

but clustered index problem are there in GUID . read this one tooNEWSEQUENTIALID() .These are my ideas ,think before use GUID as primary Key . :)

Answer 4

You can get the GUID back by using OUTPUT. This works when you're inserting multiple records also.

CREATE TABLE dbo.GuidPk (
    ColGuid uniqueidentifier NOT NULL DEFAULT NewSequentialID(),
    Col2    int              NOT NULL
)
GO

DECLARE @op TABLE (
    ColGuid uniqueidentifier
)

INSERT INTO dbo.GuidPk (
    Col2
)
OUTPUT inserted.ColGuid
INTO @op
VALUES (1)

SELECT * FROM @op

SELECT * FROM dbo.GuidPk

Reference: Exploring SQL 2005’s OUTPUT Clause