Sql-docs: How does one retrieve the newly generated GUID?

[veljkoz]

For example, if the column was marked as Identity, we can do it via @@IDENTITY, but how do we get the newly inserted value if it's marked as NEWSEQUENTIALID ?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 5a9b1a70-e8a4-9558-0e31-c665ccd761a7
• Version Independent ID: e1a957ad-dbbc-fa7a-93c7-8d3c20ec175a
• Content: NEWSEQUENTIALID (Transact-SQL) - SQL Server
• Content Source: docs/t-sql/functions/newsequentialid-transact-sql.md
• Product: sql
• Technology: t-sql
• GitHub Login: @MikeRayMSFT
• Microsoft Alias: mikeray

[MightyPen]

@MikeRayMSFT Hi Mike, I see the customer's point, that there should be a GUID analogy to @@identity.
Ideally you can contact the relevant owner who could consider providing something like @@NewSequentialId, perhaps.

[veljkoz]

In the end I resolved it in a more general way:

DECLARE @NewSequentialId UNIQUEIDENTIFIER
DECLARE @InsertedIds TABLE(Id UNIQUEIDENTIFIER)

INSERT INTO MyTable(Field1, Field2)
OUTPUT Inserted.Id INTO @InsertedIds
VALUES ('abcd', 'efgi')

SET @NewSequentialId = (SELECT TOP 1 Id FROM @InsertedIds)

(I haven't checked the syntax here, I'm typing out of my head)

Although it's a bit more typing, it worked for my case.

Thanks!

[srutzky]

FYI:

1. In most cases you should be using the SCOPE_IDENTITY() built-in function instead of @@IDENTITY. And so, any request for similar functionality for NEWID() and NEWSEQUENTIALID() would need to mirror SCOPE_IDENTITY() (e.g. "SCOPE_ID()" perhaps?)

2. The OUTPUT clause is probably the preferred approach since it can also capture new values created from multi-row inserts, something that neither @@IDENTITY nor SCOPE_IDENTITY() can do.

Take care,
Solomon..

[MikeRayMSFT]

@pmasl or @DBArgenis do you have any comment here?

[DBArgenis]

OUTPUT is the way to go here. EDIT: I meant to say for capturing the last NEWSEQUENTIALID inserted. Not addressing the identity discussion

[mburbea]

Output isn't a perfect replacement for scope_identity.
Consider, if you insert your value with an output clause? How do you get the scope_identity then?

update dbo.some_table
set some_property=@val
output 'some_table',inserted.id,'some_property',deleted.val,inserted.val into dbo.audit_trail(table,key,column,old_val,new_val)
where id = @id

How do I get the audit_trail inserted id without scope_identity() here?