如何生成自动增加UNIQUE文本ID

I have been given a task to create a table with where the CustomerID will have to be in text(Varchar) and must be unique. I created the table using the example bellow (Using calculated field). I was told that the table should not contain any calculated filed and the LEN of the CustomerID column should not exceed varchar(14)

我被赋予了一个任务来创建一个表,其中CustomerID必须是文本(Varchar),并且必须是唯一的。我使用示例波纹(使用计算字段)创建了表。我被告知该表不应包含任何计算字段,而且CustomerID列的LEN不应超过varchar(14)

CREATE TABLE dbo.Customer(
    CustID INT IDENTITY(1,1) PRIMARY KEY,
    customerID AS CAST(CustID AS VARCHAR(200))+LEFT(CONVERT(varchar(255), NEWID()),8),
    LastName varchar(300),
    FirstName varchar(300)NOT NULL
);

Guys any idea on how I can create the table with unique cutomerID without using calculated field?

伙计们如何在不使用计算字段的情况下创建具有独特cutomerID的表格?

the Output of the ID should look like

ID的输出应该是这样的

19DA9C895
2341F6E59
3B713A0CD

it can be letters or mixture of letters and numbers Thanks

它可以是字母或字母和数字的混合谢谢

5 个解决方案

#1

You can use a sequence and a default value for customer_id column. The sequence value is an integer and customer_id is a character string, but you can derive the customer_id value from the sequence value using an expression like RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14)

您可以为customer_id列使用序列和默认值。序列值是一个整数,customer_id是一个字符串,但您可以使用如右表达式('00000000000000'+ CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)),从序列值派生customer_id值,14)

Please, run the following code to see how it works:

请运行以下代码以查看其工作原理:

use tempdb;
GO

CREATE SEQUENCE customer_id_seq START WITH 1 AS bigint;

GO

SELECT RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14)

GO
CREATE TABLE customers
(
    customer_id varchar(14) DEFAULT (RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14)),
    customer_name varchar(100) NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY(customer_id)
)

GO

INSERT INTO customers(customer_name) VALUES ('ACME');
INSERT INTO customers(customer_name) VALUES ('Marvel');

GO

INSERT INTO customers(customer_id, customer_name)
VALUES (RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14), 'Fox');

INSERT INTO customers(customer_id, customer_name)
VALUES (RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14), 'Columbia');

GO
SELECT * FROM customers

Note that you can insert new rows without specifying customer_id column. But you can if you want. If you specify customer_id column you should use the expression RIGHT('00000000000000' + CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)), 14).

请注意,您可以在不指定customer_id列的情况下插入新行。但如果你愿意,你可以。如果指定customer_id列,则应使用表达式RIGHT('00000000000000'+ CAST(NEXT VALUE FOR customer_id_seq AS varchar(14)),14)。

EDIT:

A better option would be to use the expression CONVERT(varchar(14), CAST(NEXT VALUE FOR customer_id_seq AS varbinary(7)), 2). Because customer_id domain would be longer since it would contain not only digits but A, B, C, D and F characters.

更好的选择是使用表达式CONVERT(varchar(14),CAST(NEXT VALUE FOR customer_id_seq AS varbinary(7)),2)。因为customer_id域会更长,因为它不仅包含数字,还包含A,B,C,D和F字符。

The table would be:

该表将是:

CREATE TABLE customers
(
    customer_id varchar(14) DEFAULT (CONVERT(varchar(14), CAST(NEXT VALUE FOR customer_id_seq AS varbinary(7)), 2)),
    customer_name varchar(100) NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY(customer_id)
)

Insert statements:

INSERT INTO customers(customer_name) VALUES ('ACME');
INSERT INTO customers(customer_name) VALUES ('Marvel');

GO

INSERT INTO customers(customer_id, customer_name)
VALUES (CONVERT(varchar(14), CAST(NEXT VALUE FOR customer_id_seq AS varbinary(7)), 2), 'Fox');

INSERT INTO customers(customer_id, customer_name)
VALUES (CONVERT(varchar(14), CAST(NEXT VALUE FOR customer_id_seq AS varbinary(7)), 2), 'Columbia');

#2

You can use an AFTER INSERT trigger that looks at the value of CustID in inserted, and then populates CustomerID with some translation of that value (like Hexadecimal or any other formula you'd care to follow).

您可以使用AFTER INSERT触发器查看插入的CustID值,然后使用该值的某些转换(如十六进制或您需要遵循的任何其他公式)填充CustomerID。

#3

Not really sure what you are trying to do here. Your post says "auto increase", not sure what that means. But your sample output looks like random data. So I put this together to demonstrate how you can generate a 14 character random string with some criteria. I am using a tally table here to make this a bit easier. You can read more about them here. http://www.sqlservercentral.com/articles/T-SQL/62867/

不确定你在这里想做什么。你的帖子说“自动增加”,不知道这意味着什么。但您的示例输出看起来像随机数据。所以我把它放在一起演示如何用一些标准生成一个14个字符的随机字符串。我在这里使用计数表使这更容易一些。你可以在这里阅读更多相关信息。 http://www.sqlservercentral.com/articles/T-SQL/62867/

I keep a tally table as a view on my system. The code looks like this.

我保留了一个计数表作为我系统的视图。代码看起来像这样。

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

The other challenging piece here is we want get random values while using a function. This is a bit of a puzzle because the best way to get a random value is by using order by NEWID(). But you can't do that in a function. But all is not lost. We can create a view that returns a NEWID() to us and use that as the order predicate. Here is that view.

另一个具有挑战性的部分是我们想要在使用函数时获得随机值。这有点难题,因为获得随机值的最佳方法是使用NEWID()的顺序。但是你不能在一个函数中做到这一点。但一切都不会丢失。我们可以创建一个视图,向我们返回一个NEWID()并将其用作订单谓词。这是该观点。

CREATE VIEW dbo.RandomID
AS
SELECT NewId() AS RandomID

Now we just need our function. This is not pretty but the requirement is a bit offbeat as well. We need to get 14 random characters and we have to ensure that the value does not already exist in the Customer table.

现在我们只需要我们的功能。这不是很漂亮,但要求也有点不同寻常。我们需要获得14个随机字符,并且我们必须确保Customer表中不存在该值。

create function CreateAbsurdKey()
returns varchar(14) as
begin

    declare @Key varchar(14) = '';

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    select top 1 @Key = @Key + CHAR(t.N)
    from cteTally t
    where 
    (t.N >= 48 AND t.N <= 57)
        OR
    (t.N >= 65 AND t.N <= 90)
    order by (select RandomID from RandomID)

    --This will use recursion if the generated value already exists
    while exists(select * from Customer where customerID = @key)
    begin
        select @key = dbo.CreateAbsurdKey()
    end

    return @Key
end

Last but not least would be how you might be able to use this.

最后但并非最不重要的是你可以如何使用它。

insert Customer
select dbo.CreateAbsurdKey()
    , 'Lange'
    , 'Sean'

You can run that repeatedly and it will continue to insert with new values for customerID.

您可以重复运行它,它将继续插入customerID的新值。

select * from Customer

#4

My answer: To guarantee the uniqueness, it must be ensured that it does not exist in another register

我的回答:为了保证唯一性,必须确保它不存在于另一个寄存器中

CREATE TABLE Customer
(
CustID INT IDENTITY(1,1) PRIMARY KEY,
customerID VARCHAR(14) default CONVERT(VARCHAR(14), CRYPT_GEN_RANDOM(14), 2 ),
field1 VARCHAR(20)
)

INSERT INTO Customer (field1 ) VALUES ('Customer1')
INSERT INTO Customer (field1 ) VALUES ('Customer2')
INSERT INTO Customer (field1 ) VALUES ('Customer3')
INSERT INTO Customer (field1 ) VALUES ('Customer4')
INSERT INTO Customer (field1 ) VALUES ('Customer5')

SELECT * FROM Customer
GO
DROP TABLE Customer
GO

#5

CREATE TABLE dbo.Customer(
    CustID INT IDENTITY(1,1) PRIMARY KEY,
    customerID uniqueidentifier constraint [df_customer_customerID] DEFAULT(NEWID()),  --NEWID should be as unique as it gets????
    LastName varchar(300),
    FirstName varchar(300)NOT NULL
)


declare @i int = 1
while @i < 100000
begin
    insert dbo.Customer(LastName,FirstName)
    select 'Last_' + convert(varchar(10),@i), 'First_' + convert(varchar(10),@i)
    set @i = @i + 1
end

select customerID, count(1) from dbo.Customer group by customerID having count(1) > 1
select count(1) from dbo.Customer