Всем привет! Сегодня мы поговорим про объект SEQUENCE (последовательность) в Microsoft SQL Server, Вы узнаете, что такое SEQUENCE, для чего этот объект нужен, какие у него особенности, и, конечно же, научитесь им пользоваться (создавать, изменять, удалять и использовать).
- SEQUENCE в Microsoft SQL Server
- Синтаксис SEQUENCE
- Примеры создания SEQUENCE в Microsoft SQL Server
- Создание SEQUENCE со значениями по умолчанию
- Создание SEQUENCE с начальным значением 1 и увеличением на 1
- Создание SEQUENCE с начальным значением 10 и увеличением на 5
- Создание SEQUENCE с начальным значением 0 и уменьшением на 1
- Создание SEQUENCE с заданного числа и увеличением на 1
- Создание SEQUENCE с заданным типом данных
- Создание SEQUENCE с указанием максимального значения
- Создание SEQUENCE с поддержкой цикла
- Примеры изменения SEQUENCE
- Изменение параметров существующей последовательности
- Перезапуск последовательности
- Примеры использование SEQUENCE
- Простое обращение к SEQUENCE
- Использование SEQUENCE с INSERT
- Удаление SEQUENCE
- Заключение
SEQUENCE в Microsoft SQL Server
SEQUENCE – это объект SQL Server, который генерирует числовые значения в определенной последовательности в соответствии с заданной спецификацией.
В числе основных предназначений SEQUENCE является формирования значений для столбца идентификаторов в таблицах.
В Microsoft SQL Server SEQUENCE, как объект, появился только в 2012 версии, ранее для формирования значений столбцов идентификаторов в таблицах использовалось свойство IDENTITY. Теперь для этих целей можно использовать и IDENTITY, и SEQUENCE, в чем их отличие я подробно расскажу в следующих материалах.
SEQUENCE – это пользовательский объект, т.е. последовательность создают пользователи точно так же, как и другие объекты: хранимые процедуры, функции и так далее. Для создания последовательности требуются соответствующие разрешения, например, CREATE SEQUENCE. А члены предопределенных ролей db_owner и db_ddladmin по умолчанию могут выполнять любые операции с последовательностями.
Последовательность числовых значений в Microsoft SQL Server может формироваться в возрастающем или убывающем порядке с заданным интервалом. Кроме того, можно настроить перезапуск (т.е. зацикливание) последовательности, когда она исчерпана, а также задать минимальное и максимальное значения последовательности.
Значение последовательности получают с помощью вызова функции NEXT VALUE FOR, которое возвращает одно значение, однако можно получить сразу несколько значений за один раз, это делается с помощью системной процедуры sp_sequence_get_range.
SEQUENCE можно изменить уже после создания, так же, как и другие объекты, это делается с помощью инструкции ALTER SEQUENCE.
Еще одной особенностью SEQUENCE является то, что в своей работе она использует кэширование, что повышает производительность для приложений, которые используют последовательность. Это достигается за счет того, что значения последовательности заранее создаются и хранятся в памяти, а при обращении к SEQUENCE за новым значением это значение возвращается из памяти, тем самым значительно уменьшается число операций дискового ввода-вывода, которые требуются для создания значений последовательности.
Управляется кэширование последовательности с помощью параметра CACHE, который по умолчанию включен. Для его отключения необходимо указать NO CACHE.
Посмотреть, какие последовательности есть в базе данных и получить все сопутствующие сведения о последовательностях (параметры, с которыми они создавались), Вы можете с помощью обращения к системному представлению sys.sequences.
Синтаксис SEQUENCE
CREATE SEQUENCE [schema_name].[sequence_name] [ AS [data type] ] [ START WITH < value > ] [ INCREMENT BY < value > ] [ MINVALUE [ < value > ] | NO MINVALUE ] [ MAXVALUE [ < value > ] | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [ < value >] | NO CACHE ];
Описание синтаксиса
Параметр | Описание |
CREATE SEQUENCE | Инструкция создания объектов последовательности. |
schema_name и sequence_name | Имя схемы и имя последовательности. |
value | Значение параметра. |
AS | Тип данных значения, которое будет возвращать последовательность. Допускаются только целочисленные значения: TINYINT, SMALLINT, INT, BIGINT или NUMERIC с масштабом 0. Более подробно о типах данных можете посмотреть в отдельном материале – Типы данных в T-SQL (Microsoft SQL Server). Если тип данных не указан, то по умолчанию используется BIGINT. |
START WITH | Начальное значение, возвращаемое объектом последовательности. Это значение должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением у возрастающей последовательности является минимально возможное значение, а для убывающей последовательности – максимально возможное. |
INCREMENT BY | Значение, на которое увеличивается (или уменьшается) значение объекта последовательности. Если данное значение отрицательное, то объект последовательности убывает, если положительное, то возрастает. По умолчанию используется значение 1. Данное значение не может быть равно 0. |
MINVALUE | Минимальное значение объекта последовательности. По умолчанию минимальным значением для новой последовательности является минимальное значение для типа данных этой последовательности. Обращаю внимание, что для всех типов данных, кроме tinyint, минимальное значение – это отрицательное число. |
MAXVALUE | Максимальное значение объекта последовательности. По умолчанию максимальным значением для последовательности является максимальное значение типа данных объекта последовательности. |
CYCLE | Параметр показывает, должна ли последовательность быть перезапущена, как только она достигнет своего максимального или минимального значения. По умолчанию используется параметр NO CYCLE. |
CACHE | Параметр, который управляет кэшированием значений объектов последовательности. По умолчанию имеет значение CACHE. Для отключения кэширования необходимо указать NO CACHE. |
Практически все параметры являются необязательными, для создания последовательности с параметрами по умолчанию необходимо указать всего лишь инструкцию создания последовательности и имя последовательности. Однако начальное значение последовательности лучше все-таки указывать, если она будет использоваться в качестве генератора идентификаторов для таблицы.
Примеры создания SEQUENCE в Microsoft SQL Server
Теперь давайте разберём, как создается последовательность в Microsoft SQL Server.
Для выполнения примеров у меня есть база данных Test, в которой я и буду создавать последовательность.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Во всех примерах последовательность будет создаваться с названием TestSequence в схеме по умолчанию (dbo), поэтому в инструкциях я не буду указывать конкретное название схемы.
Создание SEQUENCE со значениями по умолчанию
CREATE SEQUENCE TestSequence;
Создание SEQUENCE с начальным значением 1 и увеличением на 1
CREATE SEQUENCE TestSequence START WITH 1 INCREMENT BY 1;
Создание SEQUENCE с начальным значением 10 и увеличением на 5
CREATE SEQUENCE TestSequence START WITH 10 INCREMENT BY 5;
Создание SEQUENCE с начальным значением 0 и уменьшением на 1
CREATE SEQUENCE TestSequence START WITH 0 INCREMENT BY -1;
Создание SEQUENCE с заданного числа и увеличением на 1
CREATE SEQUENCE TestSequence START WITH 12345 INCREMENT BY 1;
Создание SEQUENCE с заданным типом данных
CREATE SEQUENCE TestSequence AS TINYINT START WITH 1 INCREMENT BY 1;
Создание SEQUENCE с указанием максимального значения
CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
Создание SEQUENCE с поддержкой цикла
CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;
Примеры изменения SEQUENCE
Параметры существующего объекта SEQUENCE можно изменить. Это делается с помощью инструкции ALTER.
Изменение параметров существующей последовательности
В случае если Вам нужно изменить параметры SEQUENCE, которые Вы создали ранее, или они были созданы по умолчанию, и Вас не устраивают, то для их изменения необходимо просто указать эти параметры в инструкции ALTER SEQUENCE с новыми значениями.
Например, допустим Вы создали последовательность со значениями по умолчанию, а потом захотели уточнить минимальное, максимальное и начальное значение. Для этого Вы можете написать следующую инструкцию, в которой начальное значение, шаг приращения и минимальное значение будет 1, а максимальное 50.
ALTER SEQUENCE TestSequence RESTART WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
Перезапуск последовательности
Если Вам необходимо перезапустить последовательность, т.е. сделать так чтобы значения начались с самого начала или с определенного значения, то Вы можете выполнить следующую инструкцию.
ALTER SEQUENCE TestSequence RESTART WITH 1;
где 1 – это новое начальное значение последовательности.
Примеры использование SEQUENCE
Создавать и изменять объект SEQUENCE мы научились, теперь давайте научимся использовать его для получения значения последовательности.
Обратиться к последовательности и получить следующее значение этой последовательности можно с помощью функции NEXT VALUE FOR, которую можно использовать даже в простом запросе SELECT.
Примечание! Функцию NEXT VALUE FOR нельзя использовать в запросах с операторами DISTINCT, UNION, UNION ALL, EXCEPT, INTERSECT, а также с инструкциями FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY и FOR XML.
Простое обращение к SEQUENCE
SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence;
Использование SEQUENCE с INSERT
Сейчас давайте посмотрим, как можно использовать SEQUENCE в инструкции INSERT для генерирования значения для столбца идентификаторов в таблице.
Исходные данные для примеров
Для выполнения примеров нам потребуется таблица, в которую мы будет вставлять новые строки. В примерах будет использована таблица, созданная следующей инструкцией (для каждого примера данная таблица пересоздавалась).
CREATE TABLE Goods( ProductId INT NOT NULL, ProductName VARCHAR(100) NOT NULL );
Инструкция INSERT INTO с использованием SEQUENCE
Следующая инструкция вставляет 3 строки данных в таблицу Goods, значение ProductId мы берем из последовательности TestSequence с помощью функции NEXT VALUE FOR.
INSERT INTO Goods VALUES (NEXT VALUE FOR TestSequence, 'Компьютер'), (NEXT VALUE FOR TestSequence, 'Монитор'), (NEXT VALUE FOR TestSequence, 'Клавиатура'); SELECT * FROM Goods;
Чуть ниже я покажу результат выполнения этой инструкции в различных ситуациях, в каждой из которой SEQUENCE создавался с разным набором параметров. Тем самым, я думаю, будет наглядно видно, как работает объект последовательности с тем или иным параметром.
SEQUENCE создана со значениями по умолчанию
Как видите у нас возникла ошибка, это произошло потому, что мы не указали никаких параметров последовательности, все они были созданы со значениями по умолчанию.
Так, тип данных значения последовательности по умолчанию BIGINT, отсюда начальное значение последовательности очень большое (отрицательное) и оно не входит в диапазон значений типа данных INT, а именно этот тип данных имеет столбец ProductId в таблице Goods.
В результате мы имеем ошибку арифметического переполнения.
На заметку! Сборник статей для изучения Microsoft SQL Server
SEQUENCE создана с начальным значением 1 и увеличением на 1
SEQUENCE создана с начальным значением 10 и увеличением на 5
SEQUENCE создана с начальным значением 0 и уменьшением на 1
SEQUENCE создана с заданного числа и увеличением на 1
Здесь мы указали 12345 в качестве начального значения последовательности.
SEQUENCE создана с указанием максимального значения
CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;
В данном случае у нас возникла ошибка, так как при создании последовательности мы указали максимальное значение последовательности 50, а начальное значение 49, т.е. нам доступно всего два значения. Тем самым, когда мы пытаемся вставить третью строку, последовательность уже достигла своего максимального значения и возвращать ей просто нечего, возникает ошибка.
SEQUENCE создана с поддержкой цикла
Чтобы ошибка, которая у нас возникла выше, не появлялась, мы можем указать параметр CYCLE, тем самым, как только последовательность достигнет своего максимального значения, она будет перезапущена.
Однако здесь, конечно же, стоит учитывать возможность появления одних и тех же значений, ведь последовательность будет возвращать ровно те же самые значения, начиная с минимального.
CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;
Удаление SEQUENCE
Удаляется последовательность, так же, как и другие объекты в SQL Server, с помощью инструкции DROP. Таким образом, для удаления последовательности необходимо написать DROP SEQUENCE. Параметр IF EXISTS у инструкции DROP также можно использовать (IF EXISTS доступен, начиная с 2016 версии SQL Server).
Например, для удаления нашей тестовой последовательности можно использовать следующую инструкцию
DROP SEQUENCE IF EXISTS TestSequence;
Заключение
На сегодня это все, в следующих материалах я расскажу о том, чем отличается объект последовательности SEQUENCE от свойства IDENTITY, которое также дает нам возможность генерировать уникальные идентификаторы записей.
Удачи Вам, пока!
Приглашаю всех желающих пройти мои онлайн-курсы по изучению языка T-SQL – https://self-learning.ru/courses/t-sql
На курсах используется моя авторская последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL. Каждый курс включает огромное количество материалов: видео, текстовый материал, тесты, домашние задания, скрипты, а также сертификат о прохождении.
На курсах Вы можете заниматься в комфортном для себя темпе не выходя из дома в любое удобное для Вас время.