How to protect assignment of the same value by SCOPE_IDENTITY()?

Question

I have a problem with a situation when many users insert a record to the database in the same time and in column scope_id there are duplicated values. Is there a way to protect duplication of value?

@scope_id = SCOPE_IDENTITY()

I thought about a trigger but maybe is a simpler solution.(Checking before that id is in the database doesn't always work.)

Declare the column as `identity`, let sql-server set the values. — Serg, Mar 02 '17 at 07:34
@Serg I don't want use identity because this column is linked with another table. — Prezes Łukasz, Mar 02 '17 at 07:39
check this : http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — Abdul Rehman Sayed, Mar 02 '17 at 07:45
Can you share the table's DDL statement and the insert statement that's returning the wrong scope_id()? — Zohar Peled, Mar 02 '17 at 07:57
I don't understand the question. `SCOPE_IDENTITY` is already isolated to each connection so you wouldn't be picking up values relating to other users. — Damien_The_Unbeliever, Mar 02 '17 at 07:57
The question title is misleading: the column is NOT an identity column, and the OP does not want to use an identity column. — Giorgos Altanis, Mar 02 '17 at 08:09
What about creating a Unique index and retrying? If you don't have much load could work, although Identity or Sequence is the way to go. Identity shouldn't be a problem with Foreign Keys — Juan, Mar 02 '17 at 08:11

Giorgos Altanis · Answer 1 · 2017-03-02T08:18:10.027

2

If you are using SQL Server 12+ I would suggest to take advantage of the SEQUENCE object (MSDN reference).

Sample code from the above reference:

Creating the sequence:

CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

Using it:

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Test.CountBy1;  
-- Some work happens  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (@NextID, 'Rim', 2) ;  
GO

The NEXT VALUE statement consumes the next value of the sequence before even trying to insert a record.

edited Mar 02 '17 at 08:18

answered Mar 02 '17 at 08:07

Giorgos Altanis

2,742
1
13
14

I should say this `-- Some work happens` looks scary . They'd better say `-- Some cautiously programmed work happens`. – Serg Mar 02 '17 at 08:33

score 1 · Answer 2 · answered Mar 02 '17 at 07:58

what you are looking for can be achieved by using the T-SQL functions NEWSEQUENTIALID. This generates sequential global unique identifiers to be stored in an attribute of a UNIQUEIDENTIFIER type. The GUIDs are guaranteed to be unique across space and time

It can be used at the time of declaring the table as well For example:

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())

How to protect assignment of the same value by SCOPE_IDENTITY()?

2 Answers2