sql server: generate primary key based on counter and another column value

Question

I am creating a customer table with a parent table that is company. It has been dictated(chagrin) that I shall create a primary key for the customer table that is a combination of the company id which is an existing varchar(4) column in the customer table, e.g. customer.company

The rest of the varchar(9) primary key shall be a zero padded counter incrementing through the number of customers within that company.

E.g. where company = MSFT and this is a first insert of an MSFT record: the PK shall be MSFT00001 on subsequent inserts the PK would be MSFT00001, MSFT00002 etc. Then when company = INTL and its first record is inserted, the first record would be INTL00001

I began with an instead of trigger and a udf that I created from other stackoverflow responses.

ALTER FUNCTION [dbo].[GetNextID]
(
  @in varchar(9)
)
RETURNS varchar(9) AS
BEGIN
    DECLARE @prefix varchar(9);
    DECLARE @res varchar(9);
    DECLARE @pad varchar(9);
    DECLARE @num int;
    DECLARE @start int;


if LEN(@in)<9


 begin
   set @in = Left(@in + replicate('0',9) , 9)
  end

SET @start = PATINDEX('%[0-9]%',@in);
SET @prefix = LEFT(@in, @start - 1 );


declare @tmp int;
 set @tmp = len(@in)
 declare @tmpvarchar varchar(9);
 set @tmpvarchar = RIGHT( @in, LEN(@in) - @start + 1 )
    SET @num = CAST(  RIGHT( @in, LEN(@in) - @start + 1 ) AS int  ) + 1
    SET @pad = REPLICATE( '0', 9 - LEN(@prefix) - CEILING(LOG(@num)/LOG(10)) );
    SET @res = @prefix + @pad + CAST( @num AS varchar);

    RETURN @res
END

How would I write my instead of trigger to insert the values and increment this primary key. Or should I give it up and start a lawnmowing business?

Sorry for that tmpvarchar variable SQL server was giving me strange results without it.

Answer 1

Whilst I agree with the naysayers, the principle of "accepting that which cannot be changed" tends to lower the overall stress level, IMHO. Try the following approach.

Disadvantages

• Single-row inserts only. You won't be doing any bulk inserts to your new customer table as you'll need to execute the stored procedure each time you want to insert a row.
• A certain amount of contention for the key generation table, hence a potential for blocking.

On the up side, though, this approach doesn't have any race conditions associated with it, and it isn't too egregious a hack to really and truly offend my sensibilities. So...

First, start with a key generation table. It will contain 1 row for each company, containing your company identifier and an integer counter that we'll be bumping up each time an insert is performed.

create table dbo.CustomerNumberGenerator
(
  company     varchar(8) not null ,
  curr_value  int        not null default(1) ,

  constraint CustomerNumberGenerator_PK primary key clustered ( company ) ,

)

Second, you'll need a stored procedure like this (in fact, you might want to integrate this logic into the stored procedure responsible for inserting the customer record. More on that in a bit). This stored procedure accepts a company identifier (e.g. 'MSFT') as its sole argument. This stored procedure does the following:

• Puts the company id into canonical form (e.g. uppercase and trimmed of leading/trailing whitespace).
• Inserts the row into the key generation table if it doesn't already exist (atomic operation).
• In a single, atomic operation (update statement), the current value of the counter for the specified company is fetched and then incremented.
• The customer number is then generated in the specified way and returned to the caller via a 1-row/1-column SELECT statement.

Here you go:

create procedure dbo.GetNewCustomerNumber

  @company         varchar(8)

as

  set nocount                 on
  set ansi_nulls              on
  set concat_null_yields_null on
  set xact_abort              on

  declare
    @customer_number varchar(32)

  --
  -- put the supplied key in canonical form
  --
  set @company = ltrim(rtrim(upper(@company)))

  --
  -- if the name isn't already defined in the table, define it.
  --
  insert dbo.CustomerNumberGenerator ( company )
  select id = @company
  where not exists ( select *
                     from dbo.CustomerNumberGenerator
                     where company = @company
                   )

  --
  -- now, an interlocked update to get the current value and increment the table
  --
  update CustomerNumberGenerator
  set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) ,
      curr_value       = curr_value + 1
  where company = @company

  --
  -- return the new unique value to the caller
  --
  select customer_number = @customer_number
  return 0

go

The reason you might want to integrate this into the stored procedure that inserts a row into the customer table is that it makes globbing it all together into a single transaction; without that, your customer numbers may/will get gaps when an insert fails land gets rolled back.