sql - Generate a unique column sequence value based on a query handling concurrency

Question

I have a requirement to automatically generate a column's value based on another query's result. Because this column value must be unique, I need to take into consideration concurrent requests. This query needs to generate a unique value for a support ticket generator.

The template for the unique value is CustomerName-Month-Year-SupportTicketForThisMonthCount.

So the script should automatically generate:

• AcmeCo-10-2019-1
• AcmeCo-10-2019-2
• AcmeCo-10-2019-3

and so on as support tickets are created. How can ensure that AcmeCo-10-2019-1 is not generated twice if two support tickets are created at the same time for AcmeCo?

insert into SupportTickets (name)
  select concat_ws('-', @CustomerName, @Month, @Year, COUNT())
  from SupportTickets
  where customerName = @CustomerName
  and CreatedDate between @MonthStart and @MonthEnd;

Answer 1

One possibility:

Create a counter table:

create table Counter (
   Id int identify(1,1),
   Name varchar(64)
   Count1 int
)

Name is a unique identifier for the sequence, and in your case name would be CustomerName-Month-Year i.e. you would end up with a row in this table for every Customer/Year/Month combination.

Then write a stored procedure similar to the following to allocate a new sequence number:

create procedure [dbo].[Counter_Next]
(
  @Name varchar(64)
  , @Value int out -- Value to be used
)
as
begin
  set nocount, xact_abort on;
  
  declare @Temp int;

  begin tran;

  -- Ensure we have an exclusive lock before changing variables
  select top 1 1 from dbo.Counter with (tablockx);

  set @Value = null; -- if a value is passed in it stuffs us up, so null it

  -- Attempt an update and assignment in a single statement
  update dbo.[Counter] set
    @Value = Count1 = Count1 + 1
  where [Name] = @Name;

  if @@rowcount = 0 begin
    set @Value = 10001; -- Some starting value
    -- Create a new record if none exists
    insert into dbo.[Counter] ([Name], Count1)
      select @Name, @Value;
  end;

  commit tran;

  return 0;
end;