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

T-SQL
1
2
3
4
5
6
7
USE tempdb
GO
CREATE TABLE bla(ID INT,SomeID UNIQUEIDENTIFIER DEFAULT newsequentialid())
 
 
INSERT bla (ID) VALUES(1)
GO
USE tempdb
GO
CREATE TABLE bla(ID INT,SomeID UNIQUEIDENTIFIER DEFAULT newsequentialid())


INSERT bla (ID) VALUES(1)
GO

Do a simple select….

T-SQL
1
SELECT * FROM bla
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

T-SQL
1
2
3
INSERT bla (ID)
    OUTPUT INSERTED.SomeID
VALUES(2)
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

T-SQL
1
SELECT * FROM bla
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

T-SQL
1
2
3
4
5
6
7
DECLARE @MyTableVar TABLE( SomeID UNIQUEIDENTIFIER)
INSERT bla (ID)
    OUTPUT INSERTED.SomeID
        INTO @MyTableVar
VALUES(3)
 
SELECT SomeID FROM @MyTableVar
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

T-SQL
1
SELECT * FROM bla
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