Getting GUID Value After SQL Insert

Whoever used identity columns in SQL Server probably used SCOPE_IDENTITY in order to retrieve newest identity value after insert.

For example, if we have two columns, one named Id and declared as identity column and other named Test with text inside, we would use something like this in order to insert new row:

INSERT INTO Example(Test) VALUES('test'); SELECT SCOPE_IDENTITY();

Once we use ExecuteScalar, we would get newly inserted identity value (in column Id).

Once we enter world of Guids, we cannot do that. Guid is not considered identity value and same rules do not apply to it. In order to have similar example we shall have also two columns here. However, this time Id column will be of uniqueidentifier type and it will have it’s default set to NEWID(). This way client behavior is same. Once we insert new row (without specifying Id value) we will get Id assigned to us.

In order to retrieve value for Id column, we need to change SQL a little:

INSERT INTO Example(Test) OUTPUT INSERTED.Id VALUES('test');