sql server：根据计数器和另一列值生成主键

问题描述：

我正在使用公司的父表创建客户表。已经决定（懊恼）我将为客户表创建一个主键，该主键是客户表中现有的varchar（4）列的公司ID的组合，例如， customer.companysql server：根据计数器和另一列值生成主键

varchar（9）主键的其余部分应该是一个零填充计数器，递增该公司内的客户数量。

E.g.公司= MSFT，这是MSFT记录的第一个插入：在后续插入时，PK应为MSFT00001 PK将为MSFT00001，MSFT00002等。然后，当company = INTL并插入其第一条记录时，第一条记录会是INTL00001

我开始与一个而不是触发器和我从其他*响应创建的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服务器给了我奇怪的结果没有它。

我不会这样做。如果支持计算列（2005+？），那么我会添加一个计算列来将IDENTITY值与任何abitrary字符串作为前缀。 – 2011-01-27 21:05:47

答

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

缺点

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

从另一方面来说，这种方法没有任何与之相关的竞争条件，而且它也不是真正的冒犯我的敏感性的过分骇人听闻。所以...

首先，从一个密钥生成表开始。它将为每个公司包含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”）作为其唯一参数。此存储过程执行以下操作：

将公司标识置于规范形式（例如，大写和削减前导/尾随空白）。
如果该行尚不存在（原子操作），则将该行插入到密钥生成表中。
在单个原子操作（更新语句）中，获取指定公司的计数器的当前值，然后递增。
然后以指定的方式生成客户编号，并通过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

你可能要将此集成到插入行的客户表中的存储过程的原因是，它使得一起通配所有到一个单一的交易;否则，当插入失败的土地回滚时，您的客户数量可能会/会产生差距。

答

正如其他人在我之前所说的，使用具有计算自动增量值的主键听起来像是一个非常糟糕的主意！

如果被允许，如果你能与缺点生活（见下方），我建议如下：

使用普通的数字自动递增键和一个char（4）列其中只包含公司ID。
然后，当您从表中选择时，使用自动增量列上的row_number并将其与公司ID结合使用，以便您有一个带有“键”的附加列，该列看起来像您想要的那样（MSFT00001，MSFT00002，...）。 ..）

示例数据：

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

这将创建一个表，看起来像这样：

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

现在就可以运行下面的查询：

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

这将返回相同的表，但与你的“特殊的键”的附加列：

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

您可以创建与此查询的景色，让大家使用该视图，以确保每个人都能看到“特殊键”栏。

然而，这种解决方案有两个缺点：

你至少需要SQL Server 2005中的为了row_number工作。
从表格中删除公司时，特殊键中的数字将发生变化。因此，如果您不希望数字发生变化，您必须确保从该表中删除任何内容。

sql server：根据计数器和另一列值生成主键

相关推荐