Warning: Techie Nerd stuff to follow.

I needed the default value of a column, in this case a key, returned to me when an insert was performed on a table. With an Identity column there are several global variables to do the job, but in the case of GUIDs there wasn't anything. In this case I am required to use GUIDs, because the rows are to be universally unique for all time. Anyway we aren't here to debate my column choice. So like I was saying, I usually would be using an IDENTITY column and then I would use @@IDENTITY to return the newly generated value, but I couldn't find a method for this. I had determined that I wanted to use newsequentialid() so as to create the hot spot at the end of the table rather than in the middle. I came up with the preliminary method which wasn't pretty but it worked. Then I went to look for a better way. Someone pointed out that there is an OUTPUT clause in INSERT and found a better method and thought I would document it since we couldn't find anything out there.

Script:

CREATE TABLE test
(
ID uniqueidentifier DEFAULT NEWSEQUENTIALID(),
Blah varchar(50)
)

--If needed
CREATE TABLE id
(
ID int identity(1,1),
NSID uniqueidentifier DEFAULT newsequentialID(),
blah bit
)

--My First Script(Fucntional yet ugly)
DECLARE @NSID uniqueidentifier
INSERT id(blah)
VALUES('1')
SELECT @NSID=NSID
FROM id
WHERE ID = SCOPE_IDENTITY()

INSERT test (ID, blah)
VALUES(@NSID, ‘test1’)

SELECT @NSID

--The final version
INSERT test(blah)
OUTPUT inserted.ID
VALUES (‘test2’)