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

Posted on

sql server: generate primary key based on counter and another column value – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server,  tsql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

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.

  @in varchar(9)
RETURNS varchar(9) AS
    DECLARE @prefix varchar(9);
    DECLARE @res varchar(9);
    DECLARE @pad varchar(9);
    DECLARE @num int;
    DECLARE @start int;

if LEN(@in)<9

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

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

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.

Solution :

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.


  • 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)


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

    @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


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.

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:

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

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.

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *