Retrieve the uniqueidentifier key value for a record

Question

Hello everybody,

in context of SQL Server 2005, I have a table for which the primary key is a uniqueidentifier (GUID), with a default value generated by the newid() function. I want to write a stored procedure that inserts a new record into the table. How do I get the record's PK value? for an identity-declared field, this is easy - I call scope_identity(). How should I proceed with guids?

Thanks, Lucian

Answer 1

I would generate a new Guid proir to inserting the record, and explicitly use this new Guid as the PK for the record. You then use that guid after the insert, knowing that it refers to the row you have just inserted, e.g.

DECLARE @newGuid uniqueidentifier
SET @newGuid = newid()

INSERT INTO myTable(id, stringval)
VALUES (@newGuid, "Hello")

SELECT * FROM myTable
WHERE id = @newGuid

Answer 2

You can return it as row by using SELECT statement or use OUTPUT parameter if it's one row at a time.

Answer 3

If you are already in a stored procedure, you can retrieve the newid(), perform the insert, and return the guid:

CREATE PROCEDURE Insert_myTable(@values...) AS

DECLARE @pk GUID
SET @pk = NEWID()

INSERT INTO myTable(PKID, values...) VALUES (@pk, @values...)

SELECT @pk

Answer 4

You will have to create the unique identifier in the stored procedure and insert it into the table, then return the value after the insert is complete.

CREATE PROCEDURE insNewGuidIdRow 
    @newId UNIQUEIDENTIFIER output,
    @otherData NVARCHAR(10)
AS
BEGIN
    SET @newId = NEWID()

    INSERT INTO GuidIdTable(id, otherData)
    VALUES (@newId, @otherData)
END

It will just use the inserted value instead of creating a new guid as a default value.

Answer 5

For queries :

if your primary key column is first column Use executescalar() for execution of your insert stmt...

it will return first column of the first row in the result set returned by the query.

For procedure : INSERT INTO tblBrowser (IDString) VALUES ( @IDString) SET @ID = @@Identity declare an ado paramater specifically as a return parameter and it will receive the value returned by the sproc Set par = .CreateParameter("intOutput", adVarChar, adParamOutput, 30).

Answer 6

Generating GUID before insertion introduces index fragmentation.

There is no need to declare the GUID prior insertion,

I would recommend:

1. Using NEWSEQUENTIALID default constraint to generate contiguous Guids. (this will eliminate index fragmentation - a well known issue with Guids as primary key).

2. Use new OUTPUT clause (new in sql server 2005) which will enable you to get newly inserted GUID. Example below:

   DECLARE @outputTblCustomer1 TABLE (CustomerID uniqueidentifier)

   -- Customer1 INSERT INTO dbo.Customer1 (CustomerNumber, LastName) OUTPUT INSERTED.CustomerID INTO @outputTblCustomer1 VALUES (-1, N'LastName')

   SELECT CustomerID FROM @outputTblCustomer1

   -- Customer3 INSERT INTO dbo.Customer3 (CustomerNumber, LastName) VALUES (-1, N'LastName')

   SELECT SCOPE_IDENTITY() AS CustomerID

Answer 7

With a table like:

CREATE TABLE [dbo].[TableName ](
    [Code] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Name] [nvarchar](100) NOT NULL)

ALTER TABLE [dbo].[TableName ] ADD  CONSTRAINT [DF_Questionnaire_Code]  DEFAULT (newid()) FOR [Code]

And an insert SQL Like:

INSERT INTO TableName (Name)
OUTPUT  Inserted.Code AS NewGUID
VALUES  ('TEST')

You will get the NEW GUID as an output:

NewGUID
------------------------------------
F540C0F8-ADBC-4054-BAB6-1927DE59FA99