Retrieve the uniqueidentifier key value for a record

3

votes

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

databaseuniqueidentifier

4

votes

Generating GUID before insertion introduces index fragmentation.

There is no need to declare the GUID prior insertion,

I would recommend:

Using NEWSEQUENTIALID default constraint to generate contiguous Guids. (this will eliminate index fragmentation - a well known issue with Guids as primary key).
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

3

votes

I would generate a new Guid prior 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

1

votes

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.

0

votes

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

0

votes

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

0

votes

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).

0

votes

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

0

votes

Use this that works for me

  DECLARE @id uniqueidentifier;
  SELECT @id=NEWID()    
  INSERT INTO MyTable(ID,other)
  VALUES(@id,'other');

Retrieve the uniqueidentifier key value for a record

8 Answers