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?

Answer 1

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

Answer 2

There is no SCOPE_IDENTITY() equivalent when using GUIDs as primary keys, but you can use the OUTPUT clause to achieve a similar result. You don't need to use a table variable for output.

CREATE TABLE dbo.GuidTest (     GuidColumn uniqueidentifier NOT NULL DEFAULT NewSequentialID(),     IntColumn int NOT NULL )  GO  INSERT INTO GuidTest(IntColumn) OUTPUT inserted.GuidColumn VALUES(1) 

The example above is useful if you want to read the value from a .Net client. To read the value from .Net you would just use the ExecuteScalar method.

... string sql = "INSERT INTO GuidTest(IntColumn) OUTPUT inserted.GuidColumn VALUES(1)"; SqlCommand cmd = new SqlCommand(sql, conn); Guid guid = (Guid)cmd.ExecuteScalar(); ...