В SQL Server 2012 (ранее известном как Denali) появилась возможность работать с последовательностями. Последовательность представляет собой объект, используемый для автоматического формирования чисел для различных целей, например для ключей. В предыдущих версиях SQL Server для аналогичных целей можно было использовать свойство столбца IDENTITY, но последовательности отличаются большей гибкостью во многих отношениях. Далее я расскажу о способах их создания и использования, а также преимуществах перед IDENTITY.
Основы
Для работы с последовательностями необходимо ознакомиться с небольшим набором элементов языка: командами CREATE SEQUENCE, ALTER SEQUENCE и DROP SEQUENCE, которые используются для создания, изменения и удаления последовательностей, соответственно; функцией NEXT VALUE FOR, служащей для того, чтобы извлекать следующее значение в последовательности; процедурой sp_sequence_get_range, применяемой для защиты непрерывного диапазона значений последовательности, и представлением sys.sequences, которое используется для запроса информации о существующих последовательностях. Начнем с основных принципов последовательностей, а затем перейдем к более близкому знакомству с их свойствами.
Последовательность — независимый объект в базе данных, в отличие от IDENTITY, свойства, привязанного к определенному столбцу в конкретной таблице. Основная и типичная форма определения последовательности — указать тип данных последовательности, начальное значение и приращение, например:
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
Это довольно простая и понятная команда. В результате ее выполнения создается последовательность с именем Seq1 в схеме dbo в базе данных, с которой в данный момент существует соединение. Формируется последовательность с типом данных INT, с начальным значением 1 и приращением 1.
Чтобы получить новое значение последовательности, приложение обычно вызывает функцию NEXT VALUE FOR; например, выполните следующий фрагмент кода несколько раз, и каждый раз вы будете получать новое значение:
SELECT NEXT VALUE FOR dbo.Seq1 AS newval;
Использование выражения демонстрирует еще одно преимущество последовательностей перед IDENTITY, в дополнение к отсутствию привязки последовательностей к конкретным таблицам. Вы можете получить новое значение последовательности перед ее использованием:
DECLARE @newval AS INT = NEXT VALUE FOR dbo.Seq1; SELECT @newval;
Конечно, обычно полученное значение применяется в инструкции INSERT, но его можно использовать и для других целей.
Вернемся к команде CREATE SEQUENCE: уже отмечалось, что эта команда создает последовательность в базе данных, с которой существует соединение. Нельзя использовать имя, состоящее из трех частей, включая имя базы данных; необходимо проверить подключение к базе данных, в которой будет создана последовательность. Если вы хотите сначала проверить, существует ли такая последовательность в базе данных, и, например, удалить ее, прежде чем создавать новую, можно воспользоваться функцией OBJECT_ID и найти свойство со значением, отличным от NULL. Ниже приведен пример, в котором сначала устанавливается контекст базы данных (в данном случае AdventureWorks2008R2), удаляется объект последовательности, если он уже существует, и создается новый:
USE AdventureWorks2008R2;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
Обратите внимание, что указан тип INT; в противном случае SQL Server использовал бы BIGINT по умолчанию. Кроме того, указано начальное значение 1; в ином случае SQL Server использовал бы по умолчанию самое малое значение данного типа (например, -2147483648 для INT). Значение приращения по умолчанию – 1, и часто оно оказывается верным; и все же более естественно указать значение явно, особенно если вы уже задали начальное значение. Поэтому я указываю приращение в «типовом» определении.
В определении последовательности можно указать несколько дополнительных параметров. Полный синтаксис, как он приводится в электронной документации по SQL Server, следующий:
CREATE SEQUENCE [schema_name. ] sequence_name
[ [.. .n ] ]
[;
: :=
{
[ AS { built_in_integer_type | user-defined_integer_type } ]
| START WITH
| INCREMENT BY
| { MINVALUE | NO MINVALUE }
| { MAXVALUE | NO MAXVALUE }
| { CYCLE | NO CYCLE }
| { CACHE [ ] | NO CACHE }
}
Как мы видим, можно определить минимальное и максимальное значения в случае, если они должны отличаться от стандартных для данного типа. Кроме того, можно указать, должна ли последовательность автоматически возвращаться к началу после достижения граничного значения или выдавать исключение (что происходит по умолчанию). CACHE — более глубокий параметр, влияющий на производительность; речь о нем пойдет ниже.
Чтобы получить информацию о последовательности, направьте запрос в представление sys.sequences. Например, в следующем программном коде возвращается информация о последовательности Seq1:
SELECT current_value,
start_value, increment, minimum_value, maximum_value, is_cycling, is_cached, cache_size
FROM sys.Sequences
WHERE object_id = OBJECT_ID('dbo.Seq1');
Изменение свойств последовательности
Любое свойство существующей последовательности можно изменить с помощью команды ALTER, за исключением типа данных последовательности. Для изменения типа данных последовательности необходимо удалить или создать ее заново. Чтобы изменить свойства существующей последовательности, применяются те же ключевые слова, что в команде CREATE SEQUENCE, только вместо свойства START WITH в команде CREATE SEQUENCE используется свойство RESTART WITH в команде ALTER SEQUENCE.
Например, чтобы изменить Seq1 для перезапуска со значением -2147483648 и циклическим повтором, задействуйте следующий программный код:
ALTER SEQUENCE dbo.Seq1 RESTART WITH -2147483648 CYCLE; Чтобы начать со значения 1 и отменить циклический повтор: ALTER SEQUENCE dbo.Seq1 RESTART WITH 1 NO CYCLE;
Обратите внимание, что SQL Server не позволяет применять заведомо неверные изменения. Например, текущее значение последовательности Seq1 равно 1. Попытка изменить минимальное значение последовательности на величину, превышающую 1, без изменения текущего значения последовательности завершится неудачей. Чтобы убедиться в этом, попробуйте запустить программный код:
ALTER SEQUENCE dbo.Seq1 MINVALUE 100;
Вы получите сообщение об ошибке, показанное на экране 1.
.jpg) |
| Экран 1. Сообщение об ошибке при попытке изменить минимальное значение последовательности |
Чтобы заменить минимальное значение на 100, необходимо также заменить текущее значение таким, которое находится в допустимом диапазоне:
ALTER SEQUENCE dbo.Seq1 RESTART WITH 100 MINVALUE 100;
Выполните следующий программный код, чтобы перезапустить последовательность со значением 1 и удалите минимум:
ALTER SEQUENCE dbo.Seq1 RESTART WITH 1 NO MINVALUE;
Работа с последовательностями
Образец кода в этом разделе использует последовательность с именем Seq1 и таблицу с именем T1, которая создается с помощью следующего программного кода:
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE dbo.T1
(
col1 INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY(col1),
col2 VARCHAR(10) NOT NULL
);
Часто с помощью последовательностей создают значения, которые используются в качестве ключей в инструкциях INSERT. Выше было показано, что в отличие от IDENTITY последовательности позволяют сначала получить новое значение путем вызова выражения NEXT VALUE FOR, возможно, чтобы сохранить его в переменной, а затем использовать инструкцию INSERT. Также можно задействовать выражение NEXT VALUE FOR непосредственно в инструкции INSERT, например:
INSERT INTO dbo.T1(col1, col2) VALUES (NEXT VALUE FOR dbo.Seq1, 'A'), (NEXT VALUE FOR dbo.Seq1, 'B'), (NEXT VALUE FOR dbo.Seq1, 'C');
При желании можно определить ограничение по умолчанию для столбца с выражением NEXT VALUE FOR. Если значение для столбца не указано, ограничение получит его из последовательности. Вот пример такого ограничения:
ALTER TABLE dbo.T1 ADD CONSTRAINT DFT_T1_col1 DEFAULT (NEXT VALUE FOR dbo.seq1) FOR col1;
Обратите внимание, что эта возможность представляет собой расширение стандарта. Для тестирования ограничения выполните следующий программный код:
INSERT INTO dbo.T1(col2) VALUES('D'),('E'),('F');
SELECT * FROM dbo.T1;
В выводе должно быть шесть строк со значениями от 1 до 6 в col1. Значения 1,2 и 3 выдаются инструкцией INSERT, которая явно вызывает выражение NEXT VALUE FOR, а значения 4,5 и 6 выданы последней инструкцией INSERT с выражением DEFAULT.
Также можно использовать выражение NEXT VALUE FOR в инструкции SELECT (неважно, вставляется ли результат запроса в целевую таблицу). Например, следующий запрос выдает значение последовательности для каждой строки без вставки результата в целевую таблицу:
SELECT NEXT VALUE FOR dbo.Seq1 AS newkey, LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials FROM Person.Person WHERE PersonType = 'EM' AND EmailPromotion = 0;
Но обратите внимание, что нельзя использовать запрос, который вызывает выражение NEXT VALUE FOR в подзапросе или табличном выражении. Тот же запрос может появляться и в инструкции INSERT SELECT:
INSERT INTO dbo.T1(col1, col2) SELECT NEXT VALUE FOR dbo.Seq1 AS newkey, LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials FROM Person.Person WHERE PersonType = 'EM' AND EmailPromotion = 0;
SQL Server дополняет стандарт другим выражением, которое позволяет определить логическое упорядочение, где значения последовательности определяются с помощью предложения OVER, аналогичного используемому в оконных функциях. Поэтому, например, в следующем запросе значения последовательности назначаются на основе упорядочения BusinessEntityID:
INSERT INTO dbo.T1(col1, col2) SELECT NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY BusinessEntityID) AS newkey, LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials FROM Person.Person WHERE PersonType = 'EM' AND EmailPromotion = 1;
Другая примечательная возможность последовательностей, которой нет у IDENTITY — присвоение значений последовательности строкам в инструкции UPDATE, например:
UPDATE dbo.T1 SET col1 = NEXT VALUE FOR dbo.Seq1;
Последовательности и транзакции
Как и в случае с IDENTITY, если вы получаете новые значения последовательности в транзакции, которая в конечном итоге откатывается, изменение текущего значения последовательности не отменяется. Я продемонстрирую это на примере. Прежде всего, очистите таблицу T1 и заполните последовательность Seq1, выполнив следующий программный код:
TRUNCATE TABLE dbo.T1; ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;
Затем выполните следующий код, чтобы вставить три строки в зафиксированную транзакцию, за которой следует вставка трех строк в транзакции, которая подвергается отмене. Затем следует вставка трех строк в зафиксированную транзакцию, потом запрос к таблице:
INSERT INTO dbo.T1(col2) VALUES('A'),('B'),('C');
BEGIN TRAN
INSERT INTO dbo.T1(col2) VALUES('D'),('E'),('F');
ROLLBACK TRAN
INSERT INTO dbo.T1(col2) VALUES('G'),('H'),('I');
SELECT * FROM dbo.T1;
После выполнения программного кода выясняется, что значения в T1.col1 — 1, 2, 3, 7, 8 и 9. Изменение текущих значений последовательности, выполненное второй вставкой, не отменено, хотя выполнена отмена транзакции. Это значит, что нельзя полагаться на последовательности в случаях, когда недопустимы пропуски (например, в номерах счетов-фактур), так же, как нельзя полагаться и на IDENTITY. Если нужна последовательность без пропусков, необходимо реализовать собственное решение. Например, можно отслеживать последнее использованное значение в таблице, и каждый раз, когда требуется новое значение, увеличивать существующее с помощью инструкции UPDATE и извлекать его. Такое решение принесет блокирующую последовательность. По очевидным причинам производительность будет ниже, чем у объекта последовательности, но зато пропуски гарантированно отсутствуют.
Последовательности или IDENTITY?
У последовательностей много преимуществ по сравнению со свойством столбца IDENTITY. Я уже рассказал об основных принципах, изменении свойств последовательностей, работе с последовательностями и использовании последовательностей в транзакциях. Далее речь пойдет о кэшировании, получении диапазонов значений последовательностей и особых случаях, когда нужно сформировать несколько значений последовательности для одного целевого ряда. Сводка различий между IDENTITY и последовательностями приведена в таблице.
.jpg) |
Кэширование значений последовательности
SQL Server позволяет определить режим для последовательностей с целью повысить производительность, уменьшая число обращений к диску. Режим кэширования представляет собой расширение стандартных возможностей SQL. При создании или изменении последовательности можно указать параметр CACHE наряду со значением кэша или NO CACHE, если требуется запретить кэширование. В процессе подготовки данной статьи по умолчанию использовалось значение кэша 50; в дальнейшем оно может быть изменено. Дополнительные сведения о значениях кэша приведены во врезке «Как определить значение кэша по умолчанию в SQL Server Denali».
Если выбран режим NO CACHE (помните, что значение по умолчанию — CACHE 50), то каждый раз при создании значения последовательности SQL Server необходимо записывать изменения в системную таблицу на диске. Конечно, это плохо влияет на производительность. При использовании параметра CACHE SQL Server записывает системные таблицы только один раз при каждом запросе. Каждый раз, когда выделяется новый блок кэшированных значений, SQL Server записывает на диск первое значение, используемое в следующем блоке. На сегодня SQL Server хранит в памяти только два члена одного типа в качестве типа последовательности: текущее значение и число значений, оставшихся в текущем блоке кэшированных значений, прежде чем возникнет необходимость произвести следующую запись на диск. Каждый раз, когда появляется запрос нового значения последовательности, SQL Server обновляет эти два члена в памяти.
По сравнению с отсутствием кэширования значений можно отметить заметное повышение производительности при кэшировании даже довольно малых блоков значений (например, 50); чем больше значение кэша, тем выше производительность. Тогда почему просто не использовать максимально большое значение кэша? Потому что если произойдет неправильное завершение работы SQL Server (например, сбой питания), будет потерян оставшийся диапазон до значения, записанного на диск. Учитывая, что новые значения последовательности, сформированные в транзакции, но не зафиксированные, в конечном итоге все равно теряются, невозможно гарантировать отсутствие пробелов в последовательности.
Обратите внимание, что при корректном завершении работы SQL Server на диск записывается текущее значение плюс единица; когда после перезапуска поступает следующий запрос к значению последовательности, новое значение последовательности начинается с этой точки. Например, пусть размер кэша равен 50. Сохраненное значение в системных таблицах — 151 (первое некэшированное значение), текущее значение последовательности — 142, а число значений, оставшихся для использования, прежде чем следующий запрос приведет к записи на диск – 8. Поэтому если на данном этапе завершение работы будет выполнено корректно, SQL Server запишет число 143 (следующее значение, которое будет использовано) в системные таблицы. После перезапуска системы и появления запроса следующего значения последовательности SQL Server выделит 50 значений, записав на диск число 193.
SQL Server кэширует и значения для IDENTITY. Во время подготовки данной статьи размер кэша IDENTITY был жестко задан и равен 10, но в будущем это значение может измениться. Внутренние механизмы обработки свойства IDENTITY и последовательностей весьма схожи, но поскольку размер кэша по умолчанию 50 для последовательностей и 10 для IDENTITY, у последовательностей есть небольшое преимущество в производительности перед IDENTITY.
Существует еще одно интересное различие в кэшировании между двумя методами. Как отмечалось, в случае с последовательностями, если работа системы завершается непредвиденно, вы теряете оставшиеся кэшированные значения в текущем блоке и просто имеете пробел в значениях. В случае с IDENTITY после перезапуска SQL Server во время восстановления выполняется просмотр записей журнала, чтобы определить последнее использованное значение IDENTITY. Таким образом, оставшиеся кэшированные значения не теряются (кроме особых случаев, маловероятных из-за небольшого временного окна). Возникает вопрос, почему SQL Server не может восстановить последнее использованное значение последовательности из журнала аналогичным образом. Дело в том, что значения последовательности могут создаваться запросами SELECT без вставки в таблицу, а поэтому в журнале не существует записей для таких значений. SQL Server просто не предпринимает попыток восстановить потерянные значения кэша.
Рассмотрим некоторые показатели производительности, полученные в ходе тестирования на моем ноутбуке. Это не исчерпывающий тест, а довольно простая проверка, дающая общее представление о производительности. Воспользуйтесь исходным текстом листинга 1, чтобы создать объекты, используемые в этом тесте производительности.
Программный код в листинге 1 создает вспомогательную функцию с именем GetNums, которая возвращает последовательность целых чисел запрошенного размера. Также формируется таблица T1 со свойством IDENTITY, последовательность Seq1 и таблица T2 с выражением по умолчанию, получающим значение из Seq1.
Сначала я проверил, как много времени требуется для заполнения таблицы T2 1 000 000 строк без создания значений последовательности или IDENTITY, с помощью следующего программного кода:
TRUNCATE TABLE dbo.T2; INSERT INTO dbo.T2(col1, col2) SELECT n, 0x42 FROM dbo.GetNums(1000000);
Этот фрагмент кода был выполнен на моем ноутбуке за 7 секунд. Код выполнялся более одного раза в каждом тесте, чтобы в случае, если потребуется увеличение tempdb, этого не произошло в процессе измерений производительности.
Для определения производительности IDENTITY использовался следующий программный код:
TRUNCATE TABLE dbo.T1; INSERT INTO dbo.T1(col2) SELECT 0x42 FROM dbo.GetNums(1000000);
Выполнение данного фрагмента кода заняло 9 секунд. Таким образом, примерно 2 секунды занимает создание значений IDENTITY. Напомним, что во время подготовки данной статьи в SQL Server для IDENTITY использовалось жестко заданное значение 10; изменить его нельзя.
Затем я задействовал следующий программный код для тестирования производительности объекта последовательности с параметрами кэширования по умолчанию (CACHE 50):
TRUNCATE TABLE dbo.T2; INSERT INTO dbo.T2(col2) SELECT 0x42 FROM dbo.GetNums(1000000);
Выполнение этого фрагмента кода заняло 8 секунд. Как мы видим, производительность последовательности с параметрами кэширования по умолчанию выше, чем у IDENTITY, что можно объяснить увеличением значения кэша.
Наконец, я использовал следующий программный код для тестирования производительности последовательности без кэширования:
ALTER SEQUENCE dbo.Seq1 NO CACHE; TRUNCATE TABLE dbo.T2; INSERT INTO dbo.T2(col2) SELECT 0x42 FROM dbo.GetNums(1000000);
Время выполнения этого фрагмента кода составило 19 секунд.
Из этих тестов можно сделать вывод, что производительность объекта последовательности с кэшированием по умолчанию немного лучше производительности IDENTITY; однако без кэширования объект последовательности работает намного медленнее.
Кроме того, я выполнил тестирование различных значений кэша. Проблема с первым тестом заключается в том, что трудно выделить часть времени выполнения, связанную исключительно с формированием значений последовательности, так как большая часть времени выполнения зависит от самой операции вставки. В случае с IDENTITY у пользователя выбор невелик, поскольку необходимо вставить данные в таблицу. К счастью, работая с последовательностью, можно создать новые значения в запросе, не записывая их в таблицу.
Поэтому я направил запрос к вспомогательной функции GetNums для создания 10 000 000 строк — в 10 раз больше, чем в предыдущем тесте — и применил выражение NEXT VALUE FOR к Seq1, чтобы создать значения последовательности. В диалоговом окне Query Options («Параметры запроса») был включен режим Discard results after execution («Отбросить результаты после выполнения»), чтобы запрос времени выполнения не включал формирования выходных данных в среде SQL Server Management Studio (SSMS).
Затем я измерил время выполнения, необходимое просто для выдачи 10 000 000 строк с использованием функции, без создания значений последовательности:
SELECT n FROM dbo.GetNums(10000000);
Время выполнения этого фрагмента кода — 11 секунд. Чтобы оценить, как влияет на производительность создание только значений производительности, нужно вычесть это число из времени выполнения программного кода с формированием последовательности.
Я использовал следующий программный код для воссоздания последовательности со значением кэша по умолчанию:
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2', 'U') IS NOT NULL DROP TABLE dbo.T2;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1;
Затем я задействовал следующий код, чтобы направить запрос к функции и создать новое значение последовательности для одной строки:
SELECT NEXT VALUE FOR dbo.Seq1 FROM dbo.GetNums(10000000);
Я запускал этот фрагмент кода несколько раз, меняя определение последовательности с различными значениями кэша, которые требовалось протестировать. Я протестировал NO CACHE, 10, 20, 30, 40, 50 (по умолчанию) и 10 000 000. Результаты тестов показаны на рисунке.
.jpg) |
| Рисунок. Тест значений кэша последовательности |
Обратите внимание, что ось Y, представляющая время выполнения, начинается с 11 — время выполнения запроса без создания значений последовательности, — чтобы более наглядно отразить чистое различие в производительности между различными значениями кэша. Любопытно, что существует очень большое различие между отсутствием кэширования и кэшированием, даже для малых значений, но по мере увеличения значений кэша улучшение происходит нелинейно. Поэтому если вы опасаетесь потери большого числа значений последовательности при непредвиденном отключении, использование довольно малого значений кэша не приведет к серьезным потерям производительности. Кроме того, чтобы определить примерное местоположение IDENTITY на этой диаграмме, обратите внимание на столбик со значением кэша 10.
Затем примените следующий программный код для очистки:
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
Получение диапазона значений последовательности
В SQL Server можно запросить целый диапазон значений последовательности, обновляя последовательность лишь один раз для целого диапазона и распределяя отдельные значения с помощью приложения. Чтобы запросить новый диапазон значений последовательности, применяйте хранимую процедуру sp_sequence_get_range. Если указать во входных данных имя последовательности и размер диапазона, то в качестве выходных параметров будут получены первое значение в диапазоне, последнее значение, число циклов повторения последовательности (если разрешено), приращение последовательности и минимальное и максимальное значения. Если циклы не разрешены и если при запросе нового диапазона вы превышаете максимальное значение (минимальное при отрицательном приращении), то выдается ошибка 11732; в этом случае можно применить программный код обработки ошибок.
Рассмотрим два примера. Для начала создадим последовательность:
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS INT
START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100;
Затем выполним следующий фрагмент программного кода дважды, чтобы продемонстрировать два запроса для диапазона с размером 11:
DECLARE @first AS SQL_VARIANT; EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.Seq1', @range_size = 11, @range_first_value = @first OUTPUT; SELECT @first;
При первом запуске должно быть получено первое значение 1; при втором запуске должно быть получено первое значение 12. Конечно, в приложении обычно распределяется каждый диапазон из 11 значений. В первом случае значения от 1 до 11, во втором — значения от 12 до 22.
В определении последовательности циклы не разрешены по умолчанию. Чтобы продемонстрировать ошибку при превышении граничного значения, выполните следующий программный код:
DECLARE @first AS SQL_VARIANT; EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.Seq1', @range_size = 1759, @range_first_value = @first OUTPUT; SELECT @first;
Вы получите сообщение об ошибке, показанное на экране 2.
.jpg) |
| Экран 2. Сообщение об ошибке при превышении граничного значения |
Затем измените определение последовательности, разрешив циклы:
ALTER SEQUENCE dbo.Seq1 CYCLE;
После этого запросите диапазон, в котором превышено максимальное значение:
DECLARE @first AS SQL_VARIANT, @cycle_count AS INT; EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.Seq1', @range_size = 1759, @range_first_value = @first OUTPUT, @range_cycle_count = @cycle_count OUTPUT; SELECT @first AS first_value, @cycle_count AS cycle_count;
На этот раз операция не завершится неудачей, и будут возвращены первое значение и счетчик циклов, показанные на экране 3.
.jpg) |
| Экран 3. Выходные данные при превышении максимального значения |
Несколько значений последовательности в одной целевой строке
У функции NEXT VALUE FOR есть одна интересная особенность. Рассмотрим следующее определение последовательности:
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
Не выполняя приведенного ниже программного кода, ответьте, какими будут выходные данные?
SELECT NEXT VALUE FOR dbo.Seq1 AS val1, NEXT VALUE FOR dbo.Seq1 AS val2;
Большинство ответит «1 и 2», но, выполнив фрагмент, вы получите значения 1 и 1. Теперь, зная выходные данные, можете ли вы объяснить их?
Дело в уникальной для SQL концепции all-at-once, согласно которой все выражения, представленные на одном логическом этапе обработки запросов, концептуально оцениваются в один момент времени. Поэтому, например, можно использовать такую инструкцию UPDATE, чтобы обменять значения столбцов:
UPDATE dbo.T1 SET col1 = col2, col2 = col1;
Поскольку функция NEXT VALUE FOR является выражением, она должна следовать тем же правилам. Поэтому, сколько бы раз вы ни вызывали выражение в одном запросе, для одной строки будет во всех случаях получаться одно и то же значение.
А если имеется таблица T1 с двумя столбцами key1 и key2, которые должны получать значения из одной последовательности? Если назначить обоим столбцам выражения по умолчанию, которые применяют выражение NEXT VALUE FOR к одной последовательности, то во всех строках обоих столбцов появятся одинаковые значения. Если нужно применить к таблице однострочную инструкцию INSERT, то можно просто вызвать выражение NEXT VALUE FOR дважды в двух отдельных инструкциях, сохранить значения в переменных, а затем использовать эти переменные в инструкции INSERT.
Положение заметно усложняется, если приходится иметь дело с многострочными вставками. Сколько существует путей решения этой задачи?
Больше гибкости
У последовательностей много преимуществ перед IDENTITY, в частности они более гибкие, чем IDENTITY, и обеспечивают широкие возможности управления. Надеюсь, благодаря этой статье вы больше узнали о работе с последовательностями, а также кэшировании, получении диапазона значений и формировании нескольких значений для одной целевой строки.
Как определить значение кэша по умолчанию в SQL Server Denali
В версии SQL Server 2012 (известной под условным названием Denali) для последовательностей по умолчанию используется значение кэша 50 (эта информация отсутствует в официальной документации, так как компания Microsoft хочет сохранить за собой право изменять ее). Но как определить текущее значение кэша? Можно создать последовательность с использованием параметра кэширования по умолчанию, запросить новое значение, вызвать немедленное неправильное завершение, запустить службу SQL Server, а затем запросить новое значение и посмотреть, каким будет результат.
Чтобы вызвать немедленное завершение, можно отключить компьютер от сети, но это не самый лучший метод. Более удачный способ — просто закрыть процесс SQL Server из диспетчера задач, а затем запустить его вновь с помощью значка «Службы» панели управления. Чтобы использовать этот подход, сначала создайте новую последовательность со значением кэша по умолчанию, запросите новое значение и направьте в систему запрос текущего значения, выполнив следующий программный код:
USE AdventureWorks2008R2;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 START WITH 1;
SELECT NEXT VALUE FOR dbo.Seq1;
SELECT current_value, is_cached, cache_size
FROM sys.Sequences
WHERE OBJECT_ID = OBJECT_ID('dbo.Seq1');
Выходные данные показаны на экране A.
.jpg) |
| Экран A. Информация о?последовательности после неправильного завершения |
На данном этапе следует закрыть процесс SQL Server из диспетчера задач (только в тестовой среде!), а затем запустить службу SQL Server. После этого запросите новое значение последовательности и направьте в систему запрос о текущем значении:
USE AdventureWorks2008R2;
SELECT NEXT VALUE FOR dbo.Seq1;
SELECT current_value, is_cached, cache_size
FROM sys.Sequences
WHERE OBJECT_ID = OBJECT_ID('dbo.Seq1');
Выходные данные показаны на экране B, где мы видим, что значение кэша по умолчанию — 50.
.jpg) |
| Экран B. Выходные данные, показывающие значение кэша по умолчанию |
Конечно, этот тест непригоден для IDENTITY, так как SQL Server восстанавливает потерянные значения через журнал транзакций. В этом случае достаточно знать жестко заданное значение, которое на момент подготовки статьи было равно 10.
Листинг 1. Программный код для создания объектов
SET NOCOUNT ON;
USE tempdb;
— очистка
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
IF OBJECT_ID('dbo.T1', 'U' ) IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2', 'U' ) IS NOT NULL DROP TABLE dbo.T2;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
GO
— вспомогательная функция
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT n FROM Nums ORDER BY n OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;
GO
— T1 для IDENTITY, T2 для последовательности
CREATE TABLE dbo.T1
(
col1 INT NOT NULL IDENTITY,
col2 BINARY(1) NOT NULL
);
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE dbo.T2
(
col1 INT NOT NULL DEFAULT(NEXT VALUE FOR dbo.Seq1),
col2 BINARY(1) NOT NULL
);