Q:

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

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.

A:

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.

