SQL Server：根据计数器和另一个列值生成主键

一尘不染

SQL Server：根据计数器和另一个列值生成主键

sql

我正在创建一个母公司表与客户表的客户表。指示（恼怒）我将为客户表创建一个主键，该键是公司ID的组合，该公司ID是客户表中现有的varchar（4）列，例如customer.company

其余的varchar（9）主键应为零填充计数器，该计数器将根据该公司内的客户数量递增。

例如，如果company =
MSFT且这是MSFT记录的第一条插入：PK在随后的插入中应为MSFT00001，PK将为MSFT00001，MSFT00002等。然后，当company
= INTL并插入其第一条记录时，第一条记录将为INTL00001

我从其他stackoverflow响应创建的而不是触发器和udf开始。

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

我将如何编写触发器而不是触发器来插入值并递增此主键。还是应该放弃并开始割草业务？

很抱歉，tmpvarchar变量SQL Server在没有它的情况下给了我奇怪的结果。

阅读 232

2021-03-17

共1个答案

一尘不染

我同意反对者的观点，但“接受不能改变的原则”倾向于降低总体压力水平，恕我直言。请尝试以下方法。

缺点

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

从好的方面来看，这种方法没有任何竞争条件，并且它并不是太骇人听闻的破解方法，以至于不能真正地冒犯我的感觉。所以…

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

create table dbo.CustomerNumberGenerator
(
  company     varchar(8) not null ,
  curr_value  int        not null default(1) ,

  constraint CustomerNumberGenerator_PK primary key clustered ( company ) ,

)

其次，您将需要一个这样的存储过程（实际上，您可能希望将此逻辑集成到负责插入客户记录的存储过程中。此存储过程接受公司标识符（例如“
MSFT”）作为其唯一参数。此存储过程执行以下操作：

将公司ID设置为规范形式（例如，大写字母和前导/尾随空格修剪）。
如果该行尚不存在，则将其插入密钥生成表中（原子操作）。
在单个原子操作（更新语句）中，将获取指定公司的计数器的当前值，然后将其递增。
然后，以指定的方式生成客户编号，并通过1行/ 1列的SELECT语句将其返回给呼叫者。

干得好：

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

您可能要将其集成到将行插入到customer表中的存储过程中的原因是，它使得将它们全部聚集到一个事务中；否则，当插入失败时，您的客户数量可能/将出现缺口。

2021-03-17