虽然我同意反对者的看法，但恕我直言，接受无法改变的"原则往往会降低整体压力水平.尝试以下方法.

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.

缺点

• 仅限单行插入.您不会对新客户表进行任何批量插入，因为每次要插入行时都需要执行存储过程.
• 一定数量的密钥生成表争用，因此可能会阻塞.

不过，从好的方面来说，这种方法没有任何与之相关的竞争条件，而且它并不太严重，真正冒犯了我的敏感性.所以...

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...

首先，从密钥生成表开始.它将包含每个公司的 1 行，其中包含您的公司标识符和一个整数计数器，每次执行插入时我们都会增加该计数器.

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

)

其次，您将需要一个这样的存储过程(实际上，您可能希望将此逻辑集成到负责插入客户记录的存储过程中.稍后会详细介绍).此存储过程接受公司标识符(例如MSFT")作为其唯一参数.此存储过程执行以下操作:

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:

• 将公司 ID 转换为规范形式(例如大写并修剪前导/尾随空格).
• 如果该行不存在，则将其插入到键生成表中(原子操作).
• 在单个原子操作(更新语句)中，获取指定公司的计数器的当前值，然后递增.
• 然后以指定方式生成客户编号，并通过 1 行/1 列 SELECT 语句返回给调用方.

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

给你:

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.