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?

Answer 1

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:

Simple, and doesn't rely on anything fancy.

Answer 2

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