Insert with function result - prevent duplicates I have a MS Sql function which has some logic to process the next ID of an element (concats letters with numbers based on some business logic, also based on a select count()).

Somehow, during the last days (in production environment), since the system is being used by more concurrent users we got some doubles.

The existing logic is very simple:

insert into XXXX (A,B,C,D,E) 
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE


Somehow, the GiveMeNewId gave back the same ID in two parallel requests.

This funcion is used at the moment in different places, inside one Stored Procedure and also from an "dynamic" SQL build from a "C# client".

I do not want to implement a "singleton" layer in some middle man software.

Any ideas on how to solve this issue?
Thanks.

P.S: for testing I had the following lines inside the SQL function in order for me to run different the insert in different windows.

/*********************************************************/
declare @dtStart datetime=getutcdate()
while datediff(second,@dtStart,getutcdate()) &lt; 10
begin
    set @dtStart = @dtStart
end
/*********************************************************/
In my test scenario I think I am able to solve this problem by writting:

  insert into XXXX with (tablock, xlock) (A,B,C,D,E) .....


At least now I am not able to replicate duplicated entries, but I am not sure if I am creating some problems where I am not aware.

If you think this is not a solution, please let me know.