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

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 . :)

CREATE TABLE TestTable(KEY uniqueidentifier, ID VARCHAR(100), Name VARCHAR(100), Value tinyint);
Declare @id uniqueidentifier ; 	
DECLARE @TmpTable TABLE (KEY uniqueidentifier); 	
INSERT INTO [dbo].[TestTable]
    ([ID], [Name], Value])           
    OUTPUT INSERTED.KEY INTO @TmpTable           
    VALUES(@ID, @Name, @Value);           
SELECT @uniqueidentifier = KEY FROM @TmpTable; 
DROP TABLE TestTable;

       

Using this thread as a resource, I created the following for use within a trigger:

DECLARE @nextId uniqueIdentifier;
DECLARE @tempTable TABLE(theKey uniqueIdentifier NOT NULL DEFAULT NewSequentialID(), b int);
INSERT INTO @tempTable (b) Values(@b);
SELECT @nextId = theKey from @tempTable;

Might help someone else doing the same thing. Curious if anyone has anything bad to say performance wise if this is not a good idea or not.