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?

score 103 · Answer 1 · answered Oct 02 '09 at 16:15

103

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

answered Oct 02 '09 at 16:15

Rob Garrison

6,564
4
20
23

3

As anishmarokey mentions, you should be using NewSequentialID() to generate your GUIDs and not NewID(). – Rob Garrison Oct 02 '09 at 16:32
1

@RobGarrison Imo, GUID as a PK is only really advantageous over int/bigint in distributed systems. If you are hitting the DB to fetch an ID, you might as well use int/bigint. NewSequentialID() can only be used as a default constraint (you can't explicitly insert using NewSequentialID() for example). As such, I think the vast majority of scenarios where you *can* use it you should be doing things differently anyway. – Shiv May 14 '18 at 00:58
The `OUTPUT` clause gives an error on any table that has an insert trigger attached. – Cobus Kruger May 07 '20 at 12:09

score 65 · Answer 2 · answered Dec 06 '11 at 10:11

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

anishMarokey · Answer 3 · 2009-10-02T15:47:28.450

9

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

edited Oct 02 '09 at 15:47

answered Oct 02 '09 at 15:30

anishMarokey

10,805
2
31
45

10

"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression." – Scott Whitlock May 13 '10 at 18:46

score 4 · Answer 4 · edited Jul 27 '11 at 09:02

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;

score 2 · Answer 5 · answered Dec 31 '13 at 23:50

2

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.

answered Dec 31 '13 at 23:50

TravisWhidden

1,948
1
17
37

after re-reading the question, i realized this really doesn't answer the users question... but still might be helpful to someone because the similar answers are the same type of response. – TravisWhidden Dec 31 '13 at 23:52

SCOPE_IDENTITY() for GUIDs?

5 Answers5

Linked

Related