SCOPE_IDENTITY() for GUIDs?

sql sql-server guid uniqueidentifier scope-identity
bplus picture bplus · Oct 2, 2009 · Viewed 55k times · Source

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?

Answer

Rob Garrison picture Rob Garrison · Oct 2, 2009

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

Related questions

Aggregate Function on Uniqueidentifier (GUID)

Let's say I have the following table: category | guid ---------+----------------------- A | 5BC2... A | 6A1C... B | 92A2... Basically, I want to do the following SQL: SELECT category, MIN(guid) FROM myTable GROUP BY category It doesn't necessarily have to …

Read More
Convert GUID to varchar(32)

How can I convert a GUID which is 36 characters to a VARCHAR(32)? I'm trying to copy data from one table to another. There are two similar columns from these two tables. Table1.colx is a GUID so it is 36 characters …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More