SCOPE_IDENTITY() for GUIDs?

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?

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

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();
...