Using scope_identity() to get ID "during" insertion of row?

Question

I have a column that has an ident_current constraint on it to populate another column, like so:

alter table dbo.Employee 
add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for SourceID`

When using ident_current, I have a risk of not always getting the correct identity value in case multiple threads execute my SQL. I wanted to use scope_identity() instead. But how do I use it? I cannot simply replace ident_current with scope_identity().

alter table dbo.Employee 
add constraint dbo_Employee_D1 default scope_identity() for SourceID`

An option is to use an AFTER INSERT trigger. But won't this have the same issue in multi-threaded environment?

You want a default constraint (or trigger, or something else) that sets the SourceID column to the same value as the IDENTITY column? Why? Can you explain the actual business purpose, because I bet there is a better solution than filling a second column with redundant information. (And no, you should never use IDENT_CURRENT() for anything.) — Aaron Bertrand, Jun 26 '14 at 13:35
That column will contain the IDENTITY column value in certain cases, and will contain something else in other cases. Cannot explain the business rules here, but that is how it has to be, unfortunately. — aliensurfer, Jun 26 '14 at 14:09
So leave it NULL, populate it when it should be different, and in your queries (or a view) say COALESCE(SourceID, IdentityColumn). And don't be afraid of NULLs. — Aaron Bertrand, Jun 26 '14 at 14:12

Hannah Vernon · Answer 1 · 2014-06-26T18:07:07.917

Do exactly as Aaron said, or if you are afraid of nulls, or don't want to use COALESCE, perhaps you could do something along these lines:

USE tempdb;
GO
-- Create the table
CREATE TABLE dbo.T
(
    IdentityID INT NOT NULL CONSTRAINT PK_T PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , CopyOfIdentityID INT NULL 
);
GO
-- create a trigger to copy the values from column a to column b
CREATE TRIGGER T_Trigger ON T
AFTER INSERT
AS 
    UPDATE dbo.T SET CopyOfIdentityID = i.IdentityID FROM dbo.T INNER JOIN inserted i ON t.IdentityID = i.IdentityID;   
GO
-- insert some test values (this would typically be some other piece of code, perhaps
-- a stored proc or something.
INSERT INTO dbo.T DEFAULT VALUES;
-- you could return the SCOPE_IDENTITY() here for use in the update below.
SELECT SCOPE_IDENTITY();
-- show the row prior to changing the b column to some other value
SELECT *
FROM dbo.T;
-- update the b column to some other value.
DECLARE @SomeOtherID INT;
SET @SomeOtherID = 2;
UPDATE dbo.T SET CopyOfIdentityID = @SomeOtherID WHERE T.IdentityID = 1;
-- show the row with the updated value.
SELECT *
FROM dbo.T;

Results:

enter image description here

Simple, and doesn't rely on anything fancy.

Thanks, this is my last resort. But how would this work if multiple threads are trying to insert rows? — aliensurfer, Jun 26 '14 at 17:45
Multiple threads (concurrent inserts and updates) will work fine in this limited scenario. This is just a very small piece of code that shows a simple way of doing what you want. Without knowing your precise requirements, I cannot tell you how to implement this. — Hannah Vernon, Jun 26 '14 at 18:04
You'd need to ensure the part that updates the row later (if necessary), knows which rows to update. To accomplish this you could return the SCOPE_IDENTITY() to the caller after doing the INSERT on the table. I've updated my answer to reflect that. — Hannah Vernon, Jun 26 '14 at 18:08

score 1 · Answer 2 · answered Nov 17 '16 at 23:13

There is a Microsoft Connect suggestion requesting a function to do exactly this:

keyword/function for INSERTING a self referencing identity

It is still marked as "Active", however, there is a comment from Microsoft -- February of 2015 -- saying that they are closing it but might consider re-opening it in the future. So, I doubt it will get implemented.

On the other hand, depending on what version of SQL Server is being used, it might be possible to use a Sequence instead. In fact, there is a note on that linked MSDN page, under the Limitations section, which states:

If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQL statement. This behavior is consistent with the ANSI standard.

With that behavior in mind, we can do the following:

USE [tempdb];

CREATE SEQUENCE dbo.Seq
  AS INT
  START WITH 1
  INCREMENT BY 1;

CREATE TABLE dbo.SelfRefTest
(
  [SelfRefTestID] INT NOT NULL
    CONSTRAINT [PK_SelfRefTest] PRIMARY KEY
    CONSTRAINT [DF_SelfRefTest_SelfRefTestID] DEFAULT (NEXT VALUE FOR dbo.Seq),
  [Name] NVARCHAR(50) NOT NULL,
  [ParentSelfRefTestID] INT NULL
    CONSTRAINT [FK_SelfRefTest_SelfRefTest_SelfRefTestID] FOREIGN KEY
              REFERENCES dbo.SelfRefTest(SelfRefTestID)
    CONSTRAINT [DF_SelfRefTest_ParentSelfRefTestID] DEFAULT (NEXT VALUE FOR dbo.Seq)
);

INSERT INTO dbo.SelfRefTest ([Name]) VALUES (N'Bob');
INSERT INTO dbo.SelfRefTest ([Name]) VALUES (N'Sally');

SELECT * FROM dbo.SelfRefTest;

INSERT INTO dbo.SelfRefTest ([Name], [ParentSelfRefTestID]) VALUES (N'Sub-Bob', 1);
INSERT INTO dbo.SelfRefTest ([Name], [ParentSelfRefTestID]) VALUES (N'No Parent', NULL);

SELECT * FROM dbo.SelfRefTest;

INSERT INTO dbo.SelfRefTest ([Name], [ParentSelfRefTestID])
                   VALUES (N'Invalid FK error', 13);

Results of the second SELECT are:

SelfRefTestID    Name         ParentSelfRefTestID
-------------    ---------    -------------------
1                Bob          1
2                Sally        2
3                Sub-Bob      1
4                No Parent    NULL

Using scope_identity() to get ID "during" insertion of row?

2 Answers2