Объект SEQUENCE (последовательность) в Microsoft SQL Server

Всем привет! Сегодня мы поговорим про объект SEQUENCE (последовательность) в Microsoft SQL Server, Вы узнаете, что такое SEQUENCE, для чего этот объект нужен, какие у него особенности, и, конечно же, научитесь им пользоваться (создавать, изменять, удалять и использовать).

Объект SEQUENCE (последовательность) в Microsoft SQL Server

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, в которой я и буду создавать последовательность.

На заметку! Начинающим программистам рекомендую почитать мою книгу «Путь программиста 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;

Скриншот 1

Использование 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 создана со значениями по умолчанию

Скриншот 2

Как видите у нас возникла ошибка, это произошло потому, что мы не указали никаких параметров последовательности, все они были созданы со значениями по умолчанию.

Так, тип данных значения последовательности по умолчанию BIGINT, отсюда начальное значение последовательности очень большое (отрицательное) и оно не входит в диапазон значений типа данных INT, а именно этот тип данных имеет столбец ProductId в таблице Goods.

В результате мы имеем ошибку арифметического переполнения.

На заметку! Сборник статей для изучения Microsoft SQL Server

SEQUENCE создана с начальным значением 1 и увеличением на 1

Скриншот 3

SEQUENCE создана с начальным значением 10 и увеличением на 5

Скриншот 4

SEQUENCE создана с начальным значением 0 и уменьшением на 1

Скриншот 5

SEQUENCE создана с заданного числа и увеличением на 1

Здесь мы указали 12345 в качестве начального значения последовательности.

Скриншот 6

SEQUENCE создана с указанием максимального значения

   
   CREATE SEQUENCE TestSequence
     AS INT
     START WITH 49
     INCREMENT BY 1
     MINVALUE 1
     MAXVALUE 50;

Скриншот 7

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

SEQUENCE создана с поддержкой цикла

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

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

   
   CREATE SEQUENCE TestSequence
     AS INT
     START WITH 49
     INCREMENT BY 1
     MINVALUE 1
     MAXVALUE 50
     CYCLE;

Скриншот 8

Удаление SEQUENCE

Удаляется последовательность, так же, как и другие объекты в SQL Server, с помощью инструкции DROP. Таким образом, для удаления последовательности необходимо написать DROP SEQUENCE. Параметр IF EXISTS у инструкции DROP также можно использовать (IF EXISTS доступен, начиная с 2016 версии SQL Server).

Например, для удаления нашей тестовой последовательности можно использовать следующую инструкцию

   
   DROP SEQUENCE IF EXISTS TestSequence;

Заключение

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

Удачи Вам, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.