This question came up yesterday and I decided to do a little blog post about it. Someone wanted to know if there was something like @@identity/scope_identity() for a uniqueidentifier column with a default of newsequentialid(). There is not such a function but you can use OUTPUT INSERTED.Column to do something similar. Let’s take a look

First create this table

USE tempdb
GO
CREATE TABLE bla(ID INT,SomeID UNIQUEIDENTIFIER DEFAULT newsequentialid())


INSERT bla (ID) VALUES(1)
GO

Do a simple select….

SELECT * FROM bla

As you can see we have 1 row

ID SomeID
1   D6911D8A-0AE6-E011-A428-0021867E1D41

Here is what the insert looks like that also returns the uniqueidentifier just created by the newsequentialid()default

INSERT bla (ID)
    OUTPUT INSERTED.SomeID
VALUES(2)

SomeID
28E798A8-0AE6-E011-A428-0021867E1D41

As you can see you get the uniqueidentifier just created back, all we have added was OUTPUT INSERTED.SomeID between INSERT….. and VALUES……

Pretty simple so far

Now, we should have two rows in the table

SELECT * FROM bla

ID SomeID
1   D6911D8A-0AE6-E011-A428-0021867E1D41
2   28E798A8-0AE6-E011-A428-0021867E1D41

You can also populate a table variable and then use that to return the values

DECLARE @MyTableVar TABLE( SomeID UNIQUEIDENTIFIER)
INSERT bla (ID)
    OUTPUT INSERTED.SomeID
        INTO @MyTableVar
VALUES(3)
 
SELECT SomeID FROM @MyTableVar

SomeID
D26351C1-0AE6-E011-A428-0021867E1D41

Finally we can run a select that confirms all 3 inserts actually have happened

SELECT * FROM bla

Here is the data, of course on your machine the values for SomeID won’t be the same

ID SomeID
1   D6911D8A-0AE6-E011-A428-0021867E1D41
2   28E798A8-0AE6-E011-A428-0021867E1D41
3   D26351C1-0AE6-E011-A428-0021867E1D41

That is it for this post, for some more OUTPUT examples take a look at Using T-SQL OUTPUT and MERGE To Link Old and New Keys