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

Question

I am creating a customer table with a parent table, which is a company. It was dictated (chagrin) that I will create a primary key for the customer table, which is a combination of the company identifier, which is the existing varchar (4) column in the customer table, for example. customer.company

The rest of the varchar (9) primary key must be a complement with a zero complement, increasing in number of customers within this company.

eg. where company = MSFT, and this is the first insertion of the MSFT record: the PC must be MSFT00001, the subsequent insertions of the PC will be MSFT00001, MSFT00002, etc. Then, when company = INTL and its first record is inserted, the first record will be INTL00001

I started with the trigger and udf instead, which I created from other stackoverflow answers.

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 do I write instead of a trigger to insert values ​​and increase this primary key. Or should I give it up and start a lawn mower?

Sorry for the tmpvarchar SQL Server SQL variable giving me weird results without it.

Answer 1

As long as I agree with skeptics, the principle of “accepting that which cannot be changed” tends to reduce the overall level of stress, IMHO. Try the following approach.

disadvantages

• Single line inserts only. You will not do bulk inserts into the new client table, since you will need to execute the stored procedure every time you want to insert a row.
• A certain degree of competition for the key generation table, therefore, the possibility of blocking.

However, on the other hand, this approach does not have any race conditions associated with it, and it is not a too egregious hack to really and really offend my feelings. So that...

Start with the key generation table. It will contain 1 line for each company, containing the identifier of your company and an integer counter, which we will click every time the 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 ) , ) 

Secondly, you will need a stored procedure like this (in fact, you may need to integrate this logic into a stored procedure that is responsible for inserting a client record. A little more on that). This stored procedure takes a company identifier (for example, "MSFT") as a single argument. This stored procedure performs the following actions:

• Inserts a company identifier in canonical form (for example, in uppercase and truncates leading / trailing spaces).
• Inserts a row into the key generation table if it does not already exist (atomic operation).
• In one atomic operation (update instruction), the current counter value for the specified company is selected and then increased.
• Then, the client number is generated in this way and returned to the caller using the SELECT with 1 row / 1 column.

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 can integrate this into a stored procedure that inserts a row into a client table is because it combines all of this into a single transaction; without this, your customer numbers may / will have spaces when the insert fails to return the land.

Answer 2

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

If you are allowed, and if you can live with minuses (see below), I would suggest the following:

Use a regular numeric key for automatic increment and a column char (4), which contains only the company identifier.
Then, when you select from the table, you use row_number in the auto-increment column and combine this with the company identifier so that you have an additional column with a “key” that looks the way you like (MSFT00001, MSFT00002, ...)

Sample 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:

 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 extra 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 can create a view with this query and let everyone use this view to make sure everyone sees the "special key" column.

However, this solution has two drawbacks:

• You need at least SQL Server 2005 to order row_number to work.
• The numbers in the special key will change when companies are removed from the table. Thus, if you do not want the numbers to change, you must make sure that nothing is ever deleted from this table.