in context of SQL Server 2005, I've got a table that the main secret is a uniqueidentifier (GUID), having a default value produced through the newid() function. I wish to write a saved method that card inserts a brand new record in to the table. How do you obtain the record's PK value? to have an identity-declared area, this really is easy - I call scope_identity(). How must i proceed with guids?
Producing GUID before insertion introduces index fragmentation.
There's you don't need to declare the GUID prior insertion,
I would suggest:
- Using NEWSEQUENTIALID default constraint to create contiguous Guids. (this can eliminate index fragmentation - a common problem with Guids as primary key).
Use new OUTPUT clause (new in sql server 2005) that will allow you to get recently placed GUID. Example below:
DECLARE @outputTblCustomer1 TABLE (CustomerID uniqueidentifier)
-- Customer1 Place INTO dbo.Customer1 (CustomerNumber, LastName) OUTPUT Placed.CustomerID INTO @outputTblCustomer1 VALUES (-1, N'LastName')
Choose CustomerID FROM @outputTblCustomer1
-- Customer3 Place INTO dbo.Customer3 (CustomerNumber, LastName) VALUES (-1, N'LastName')
Choose SCOPE_IDENTITY() AS CustomerID
I'd produce a new Guid proir to placing the record, and clearly make use of this new Guid because the PK for that record. After this you use that guid following the place, understanding that it refers back to the row you've just placed, e.g.
DECLARE @newGuid uniqueidentifier SET @newGuid = newid() INSERT INTO myTable(id, stringval) VALUES (@newGuid, "Hello") SELECT * FROM myTable WHERE id = @newGuid
You'll have to produce the unique identifier within the saved procedure and place it in to the table, then return the worthiness following the place is done.
CREATE PROCEDURE insNewGuidIdRow @newId UNIQUEIDENTIFIER output, @otherData NVARCHAR(10) AS BEGIN SET @newId = NEWID() INSERT INTO GuidIdTable(id, otherData) VALUES (@newId, @otherData) END
It'll only use the placed value rather than developing a new guid like a default value.
You are able to give it back as row by utilizing
SELECT statement or use
OUTPUT parameter whether it's one row at any given time.
If you're already inside a saved procedure, you are able to retrieve the newid(), carry out the place, 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
For queries :
in case your primary key column is first column Use executescalar() for execution of the place stmt...
it'll return first column from the first row within the result set came back through the query.
For procedure : Place INTO tblBrowser (IDString) VALUES ( @IDString) SET @ID = @@Identity declare an ado paramater particularly like a return parameter and it'll get the value came back through the sproc Set componen = .CreateParameter("intOutput", adVarChar, adParamOutput, 30).
Having 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]
As well as an place SQL Like:
INSERT INTO TableName (Name) OUTPUT Inserted.Code AS NewGUID VALUES ('TEST')
You're going to get the brand new GUID being an output:
NewGUID ------------------------------------ F540C0F8-ADBC-4054-BAB6-1927DE59FA99