Последовательности ключей в T-SQL

52 Исходник базы данных

В самых разных ситуациях может потребоваться генерировать последовательности уникальных целых ключей при обновлении или вставке данных в таблицу. В SQL Server 2012 появилась поддержка объектов последовательности, что позволяет решить часть подобных задач. Однако объектов последовательности нет в более ранних версиях SQL Server. Кроме того SQL Server не сможет откатить генерацию последовательных значений, если транзакция, в которой они генерируются, потерпит сбой, — иначе говоря, могут возникать пробелы в последовательности значений. (Такая же ситуация с IDENTITY.) Если нужно, чтобы гарантировано не было никаких пробелов в последовательности сгенерированных ключей, объекты последовательности использовать нельзя. В этой статье я покажу, как решить ряд задач, в которых нужны последовательности значений, не прибегая к объектам последовательности.

Обновление столбца с заполнением уникальными значениями

В первом сценарии решается вопрос качества данных. Выполните следующий код, чтобы создать и заполнить таблицу по имени MyOrders, которую мы будем использовать в качестве примера данных:

IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL
  DROP TABLE Sales.MyOrders;
GO

SELECT 0 AS orderid, custid, empid, orderdate
INTO Sales.MyOrders
FROM Sales.Orders;

SELECT * FROM Sales.MyOrders;
Результат запроса с генерацией таблицы MyOrders

Представьте, что из-за проблем с качеством данных в атрибуте orderid таблицы MyOrders значения неуникальны. Вам нужно обновить все строки, добавив уникальные целые значения в произвольном порядке, начиная с единицы. Для решения этой задачи можно определить CTE на основе запроса MyOrders, возвращающего атрибут orderid, а также значение ROW_NUMBER. Если нет требования упорядочивать при вычислении номеров строк, можно использовать (SELECT NULL) в предложении упорядочения окна. Затем во внешнем запросе CTE применим инструкцию UPDATE, которая присваивает orderid результат ROW_NUMBER:

WITH C AS
(
  SELECT orderid, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
  FROM Sales.MyOrders
)
UPDATE C
  SET orderid = rownum;

Если запросить таблицу MyOrders после обновления, можно увидеть, что значения orderid теперь уникальны:

SELECT * FROM Sales.MyOrders;
Результат запроса после обновления MyOrders

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

Получение блока значений последовательности

Допустим вам нужен механизм, гарантировано создающий последовательности без пробелов. Нельзя полагаться на столбец идентификационного свойства или объект последовательности, потому что оба механизма не исключают пробелов, которые образуются когда, к примеру, происходит сбой значениях последовательности или значение не фиксируется. Один из стандартных способов реализации механизма создания последовательности, который гарантирует отсутствие пробелов, — хранить последнее использованное значение в таблице и каждый раз, когда нужно новое значение, инкрементировать хранимое значение и использовать его в качестве нового.

Например, следующий код создает таблицу по имени MySequence и размещает в ней одну строку со значением «0» в столбце val:

IF OBJECT_ID('dbo.MySequence', 'U') IS NOT NULL DROP TABLE dbo.MySequence;
CREATE TABLE dbo.MySequence(val INT);
INSERT INTO dbo.MySequence VALUES(0);

Когда нужно сгенерировать и использовать новое значение последовательности, можно применить такую хранимую процедуру:

IF OBJECT_ID('dbo.GetSequence', 'P') IS NOT NULL DROP PROC dbo.GetSequence;
GO

CREATE PROC dbo.GetSequence
  @val AS INT OUTPUT
AS
UPDATE dbo.MySequence
  SET @val = val += 1;

Эта процедура обновляет строку в MySequence, увеличивая текущее значение на единицу, и сохраняет результат в выходном параметре @val. Каждый раз, когда требуется новое значение последовательности, достаточно выполнить эту процедуру и взять готовое значение из выходного параметра:

DECLARE @key AS INT;
EXEC dbo.GetSequence @val = @key OUTPUT;
SELECT @key;

Если выполнить этот код дважды (естественно, в одной транзакции), вы получите значение 1, а затем 2.

Представьте себе, что иногда нужно выделить целый блок последовательных значений, например при вставке в таблицу нескольких строк. Прежде всего нужно изменить процедуру, предусмотрев в ней дополнительный входной параметр (назовем его @n), который указывает размер блока. После этого процедура может инкрементировать столбец val в MySequence на величину @n и возвращать новое значение нового блока в качестве выходного параметра. Вот измененное определение процедуры:

ALTER PROC dbo.GetSequence
  @val AS INT OUTPUT,
  @n   AS INT = 1
AS
UPDATE dbo.MySequence
  SET @val = val + 1,
       val += @n;

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

SELECT custid
FROM Sales.Customers
WHERE country = N'UK';
Результат запроса выборки клиентов из Великобритании

От вас требуется сгенерировать суррогатные ключи для этих клиентов и, в конечном итоге, вставить записи о клиентах в размерность клиентов своего хранилища данных. Можно разместить этот результирующий набор в табличной переменной вместе с результатом функции ROW_NUMBER, которая сгенерирует уникальные целые значения, начинающиеся с единицы. (Назовем этот столбец rownum.) Затем можно разместить число задействованных строк из функции @@rowcount в локальную переменную (назовем ее @rc). Затем можно вызвать процедуру, передав ей @rc в качестве размера блока, и получить первый ключ в блоке, а затем разместить его в локальной переменной (назовем ее @firstkey). Наконец, можно запросить табличную переменную и вычислить конкретные значения последовательность с помощью выражения @firstkey + rownum - 1. Вот код T-SQL всего решения:

DECLARE @firstkey AS INT, @rc AS INT;

DECLARE @CustsStage AS TABLE
(
  custid INT,
  rownum INT
);

INSERT INTO @CustsStage(custid, rownum)
  SELECT custid, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
  FROM Sales.Customers
  WHERE country = N'UK';

SET @rc = @@rowcount;

EXEC dbo.GetSequence @val = @firstkey OUTPUT, @n = @rc;

SELECT custid, @firstkey + rownum - 1 AS keycol
FROM @CustsStage;
Результат запроса с генерацией суррогатных ключей

Естественно, последняя часть должна вставлять результаты этого запроса в целевую таблицу. Также заметьте, что я использую выражение ORDER BY (SELECT NULL) в инструкции упорядочения окна в функции ROW_NUMBER, чтобы получить произвольный порядок номеров строк. Если значения последовательности надо назначать в определенном порядке (например, при упорядочении по custid), измените соответствующим образом инструкцию упорядочения окна.

Затем выполните похожий процесс, но на этот раз используйте в качестве источника клиентов из Франции:

DECLARE @firstkey AS INT, @rc AS INT;

DECLARE @CustsStage AS TABLE
(
  custid INT,
  rownum INT
);

INSERT INTO @CustsStage(custid, rownum)
  SELECT custid, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
  FROM Sales.Customers
  WHERE country = N'France';

SET @rc = @@rowcount;

EXEC dbo.GetSequence @val = @firstkey OUTPUT, @n = @rc;

SELECT custid, @firstkey + rownum - 1 AS keycol
FROM @CustsStage;
Результат запроса выборки клиентов из Франции

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

По завершении надо выполнить следующий код очистки:

IF OBJECT_ID('dbo.GetSequence', 'P') IS NOT NULL DROP PROC dbo.GetSequence;
IF OBJECT_ID('dbo.MySequence', 'U') IS NOT NULL DROP TABLE dbo.MySequence;
Пройди тесты
Лучший чат для C# программистов