Уникальный идентификатор строки t-sql (база данных Northwind)

Question

Я пытался понять это в течение некоторого времени без толку.

У меня есть таблица в базе данных mssql, и я хочу вставить новую строку с помощью хранимой процедуры

CREATE TABLE "Customers" (
"CustomerID" NCHAR(5) NOT NULL,
"CompanyName" NVARCHAR(40) NOT NULL,
"ContactName" NVARCHAR(30) NULL,
"ContactTitle" NVARCHAR(30) NULL,
"Address" NVARCHAR(60) NULL,
"City" NVARCHAR(15) NULL,
"Region" NVARCHAR(15) NULL,
"PostalCode" NVARCHAR(10) NULL,
"Country" NVARCHAR(15) NULL,
"Phone" NVARCHAR(24) NULL,
"Fax" NVARCHAR(24) NULL,
PRIMARY KEY ("CustomerID")
);

Проблема в поле CustomerID, которое содержит уникальную строку для каждой записи (ALFKI, BERGS, BERGS и т. Д.)

Я хочу сделать хранимую процедуру, которая вставит строку с новыми данными и создаст уникальный CustomerID. О встроенных функциях не может быть и речи, так как мне нужна строка длиной 5 символов.

У меня есть процедура, которая генерирует 5 символов ID следующим образом

begin

declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(max) = ''

while @i < 5
begin
        set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)

    set @i = @i + 1
end

Select (cast(@id as nvarchar(400)))

end

И тот, который я пытался заставить работать без толку. Предполагается выбрать уникальный идентификатор (set @id = 'ANATR' специально для того, чтобы он вошел в цикл

begin
declare @randID varchar(5) = ''
declare @selectID varchar(20) = ''
declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(10) = ''

while @i < 5
begin
   set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
    set @i = @i + 1
end
select @id
set @id = 'ANATR'

SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)


while @selectID <> 'NULL'
begin
    set @id = ''
    while @i < 5
        begin
            set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
            set @i = @i + 1
        end

    SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)   

    SELECT @id
end


end

Вот процедура вставки у меня на данный момент

CREATE PROCEDURE [dbo].[InsertCustomers]

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30) = NULL,

@ContactTitle nvarchar(30) = NULL,

@Address nvarchar(60) = NULL,

@City nvarchar(15) = NULL,

@Region nvarchar(15) = NULL,

@PostalCode nvarchar(10) = NULL,

@Country nvarchar(15) = NULL,

@Phone nvarchar(24) = NULL,

@Fax nvarchar(24) = NULL

)

AS

SET NOCOUNT OFF;

 INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

Answer 1

Я считаю, что вы можете сделать что-то подобное, чтобы убедиться, что вы все получили уникальный идентификатор

begin

declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(max) = ''


while (1=1)
begin
        set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)

    set @i = @i + 1

   IF (NOT EXISTS(SELECT * FROM Customers WHERE CustomerID = @id) AND LEN(@id) = 5)
      BREAK
   ELSE
      CONTINUE
end

Select (cast(@id as nvarchar(400)))

end

Установите условие while, чтобы оно всегда было истинным, и выход из цикла while только тогда, когда оба ваших требования TRUE, т.е. Length of new ID is 5 и это does not exist in the customers table already,

Answer 2

Основная проблема здесь заключается в том, что дополнительные затраты на обнаружение коллизий из сгенерированной строки и повторную попытку возрастают по мере того, как вы генерируете все больше и больше строк (поскольку вам нужно прочитать все эти строки, чтобы убедиться, что вы не сгенерировали дубликат), В то же время вероятность попадания в дубликат возрастает, то есть, чем больше таблица, тем медленнее будет этот процесс.

Зачем вам нужно генерировать уникальную строку во время выполнения? Постройте их все заранее. Эта статья и этот пост о случайных числах, но основная концепция одинакова. Вы создаете набор уникальных строк и вытаскиваете одну из стека, когда она вам нужна. Ваш шанс коллизий остается постоянным на уровне 0% на протяжении всего жизненного цикла приложения (при условии, что вы создадите стек с достаточным количеством уникальных значений). Оплатите стоимость столкновений заранее, в своей собственной настройке, а не постепенно с течением времени (и за счет пользователя, ожидающего этих попыток, чтобы в итоге получить уникальное число).

Это сгенерирует 100000 уникальных 5-символьных строк по низкой единовременной цене около 1 секунды (на моей машине):

;WITH 
 a(a) AS 
 (
   SELECT TOP (26) number + 65 FROM master..spt_values 
   WHERE type = N'P' ORDER BY number
 ),
 b(a) AS 
 (
   SELECT TOP (10) a FROM a ORDER BY NEWID()
 )
SELECT DISTINCT CHAR(b.a) + CHAR(c.a) + CHAR(d.a) + CHAR(e.a) + CHAR(f.a)
FROM b, b AS c, b AS d, b AS e, b AS f;

Этого не достаточно? Вы можете создать около 1,12 миллиона уникальных значений, изменив TOP (10) в TOP (20), Это заняло 18 секунд. Все еще недостаточно? TOP (24) даст вам чуть менее 8 миллионов за 2 минуты. Это будет экспоненциально дороже, поскольку вы генерируете больше строк, потому что это DISTINCT должен делать ту же проверку дубликатов, которую вы хотите делать каждый раз, когда вы добавляете клиента.

Итак, создайте таблицу:

CREATE TABLE dbo.StringStack
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  String CHAR(5) NOT NULL UNIQUE
);

Вставьте этот набор:

;WITH 
 a(a) AS 
 (
   SELECT TOP (26) number + 65 FROM master..spt_values 
   WHERE type = N'P' ORDER BY number
 ),
 b(a) AS 
 (
   SELECT TOP (10) a FROM a ORDER BY NEWID()
 )
INSERT dbo.StringStack(String)
SELECT DISTINCT CHAR(b.a) + CHAR(c.a) + CHAR(d.a) + CHAR(e.a) + CHAR(f.a)
FROM b, b AS c, b AS d, b AS e, b AS f;

А затем просто создайте процедуру, которая выталкивает одну из стека, когда вам это нужно:

CREATE PROCEDURE dbo.AddCustomer
  @CustomerName VARCHAR(64) /* , other params */
AS
BEGIN
  SET NOCOUNT ON;

  DELETE TOP (1) dbo.StringStack
    OUTPUT deleted.String, @CustomerName /* , other params */
    INTO dbo.Customers(CustomerID, CustomerName /*, ...other columns... */);
END
GO

Нет глупых циклов, нет необходимости проверять, CustomerID вы сгенерировали просто существует и т. д. Единственная дополнительная вещь, которую вы хотите создать, - это какой-то тип проверки, который уведомляет вас, когда вы получаете низкий уровень.

Кроме того, это ужасные идентификаторы для CustomerID. Что не так с последовательным суррогатным ключом, таким как столбец IDENTITY? Как 5-значная случайная строка со всеми этими усилиями может быть лучше, чем уникальное число, которое система может сгенерировать для вас гораздо проще?

Answer 3

Ответ Мухаммеда Али работает, но он окажется довольно ресурсоемким (особенно, когда осталось не так много комбинаций из 5 букв): ваша функция использует генератор случайных чисел, и потребуется некоторое время, чтобы найти комбинацию, которая не не используется, тем более, что он имеет очень ограниченную память о своих предыдущих результатах. Это означает, что он попытается и может дать вам что-то в этом роде (немного преувеличивая): BAGER в первый раз, затем ОТВЕТЬТЕ во второй раз, затем снова BAGER в третий раз. Вы видите, что вы потеряете много времени, когда генератор будет давать вам один и тот же ответ снова и снова (особенно более 12 миллионов возможных комбинаций).

Если вы ищете идентификатор фиксированной длины (так как вы используете NCHAR(5), я думаю, это хорошее предположение), я бы лучше посмотрел на создание таблицы, которая содержит все возможные комбинации, и каждый раз выбирал одно значение этой таблицы тебе нужен один Вы удалите его, как только он будет использован, или пометите его как использованный (что я бы предпочел, по причинам повторного использования).

Это приводит к моему последнему комментарию (который я не могу поместить в качестве комментария, потому что у меня недостаточно репутации): почему бы не использовать функцию IDENTITY, предоставляемую MS-SQL? Это обеспечивает намного лучшую обработку генерации первичного ключа...