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.

Answer 2

As others said before me, using a primary key with calculated auto-increment values sounds like a very bad idea!

If you are allowed to and if you can live with the downsides (see at the bottom), I would suggest the following:

Use a normal numeric auto-increment key and a char(4) column which only contains the company id.
Then, when you select from the table, you use row_number on the auto-increment column and combine that with the company id so that you have an additional column with a "key" that looks like you wanted (MSFT00001, MSFT00002, ...)

Example data:

create table customers
(
    Id int identity(1,1) not null,
    Company char(4) not null,
    CustomerName varchar(50) not null
)

insert into customers (Company, CustomerName) values ('MSFT','First MSFT customer')
insert into customers (Company, CustomerName) values ('MSFT','Second MSFT customer')
insert into customers (Company, CustomerName) values ('ABCD','First ABCD customer')
insert into customers (Company, CustomerName) values ('MSFT','Third MSFT customer')
insert into customers (Company, CustomerName) values ('ABCD','Second ABCD customer')

This will create a table that looks like this:

Id   Company CustomerName
------------------------------------
1   MSFT    First MSFT customer
2   MSFT    Second MSFT customer
3   ABCD    First ABCD customer
4   MSFT    Third MSFT customer
5   ABCD    Second ABCD customer

Now run the following query on it:

select 
    Company + right('00000' + cast(ROW_NUMBER() over (partition by Company order by Id) as varchar(5)),5) as SpecialKey,
    * 
from
    customers

This returns the same table, but with an additional column with your "special key":

SpecialKey   Id  Company CustomerName
---------------------------------------------
ABCD00001   3   ABCD    First ABCD customer
ABCD00002   5   ABCD    Second ABCD customer
MSFT00001   1   MSFT    First MSFT customer
MSFT00002   2   MSFT    Second MSFT customer
MSFT00003   4   MSFT    Third MSFT customer

You could create a view with this query and let everyone use that view, to make sure everyone sees the "special key" column.

However, this solution has two downsides:

1. You need at least SQL Server 2005 in order for row_number to work.
2. The numbers in the special key will change when you delete companies from the table. So, if you don't want the numbers to change, you have to make sure that nothing is ever deleted from that table.