что такое sequence в sql

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

Всем привет! Сегодня мы поговорим про объект SEQUENCE (последовательность) в Microsoft SQL Server, Вы узнаете, что такое 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

Описание синтаксиса

Практически все параметры являются необязательными, для создания последовательности с параметрами по умолчанию необходимо указать всего лишь инструкцию создания последовательности и имя последовательности. Однако начальное значение последовательности лучше все-таки указывать, если она будет использоваться в качестве генератора идентификаторов для таблицы.

Примеры создания SEQUENCE в Microsoft SQL Server

Теперь давайте разберём, как создается последовательность в Microsoft SQL Server.
Для выполнения примеров у меня есть база данных Test, в которой я и буду создавать последовательность.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

Во всех примерах последовательность будет создаваться с названием TestSequence в схеме по умолчанию (dbo), поэтому в инструкциях я не буду указывать конкретное название схемы.

Источник

MS SQL 2011 – новый объект Sequence

Возможность, которой не удивишь нынче пользователей Oracle, DB2, PostgreSQL и множества других реляционных баз данных, наконец-то появилась и в MS SQL Server. На арене Sequence!

Sequence – генерирует последовательность чисел так же как и identity. Однако основным плюсом sequence является то, что последовательность не зависит от какой-либо конкретной таблицы и является объектом базы данных.

Рассмотрим пример скрипта написанного на SQL Server 2008. Создание простой таблицы с двумя колонками, одна из которых будет автоинкрементной.

Похожим образом создадим еще одну таблицу.

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

Общий синтаксис для команды выглядит так:

Создадим последовательность чисел:

После выполнения указанного скрипта, в браузере объектов базы, в узле Sequences можно найти наш объект.

После того как объект создан, можно его использовать в создании и заполнении таблиц как показано ниже:

Если создать вторую таблицу в таком же духе, то можно снова использовать GenerateNumberSequence и получать сквозную нумерацию объектов.

Последовательность (Sequence) которую мы создали, можно посмотреть в системном каталоге sys.sequences.

Это не вся доступная информация по sequence, просто эти колонки нам понадобятся далее. Чтобы получить всю информацию замените имена колонок на звездочку. Про Is_Exhausted будет упомянуто позднее.

Проверим на практике, что скажет SQL Server при задании начального числа вне допустимого диапазона. Начнем с левой границы.

An invalid value was specified for argument ‘START WITH’ for the given data type.

Что и ожидалось. Теперь нарушим правую границу.

Сервер сообщит нам об ошибке так:

The sequence object ‘GenerateNumberSequence’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

И если мы обратим внимание на колонку Is_Exhausted в каталоге sys.sequences, то увидим, что значение стало равно 1. Что говорит нам о невозможности дальнейшего использования данной последовательности.

При попытке создать таблицу с использованием такой последовательности, сервер выдаст ошибку:

The sequence object ‘GenerateNumberSequence’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Это можно трактовать как просьбу движка рестартовать указанную последовательность. Для этого необходимо воспользоваться конструкцией RESTART WITH.

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

А потом выполнить скрипт:

То результат будет таким:

Последовательность началась с заданного значения.

Получить минимальные и максимальные значения можно из каталога sys.sequences.

MIN и MAX значения

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

Минимальное значение равняется 10, максимальное – 20, но мы пытаемся задать начальное значение равное единице. Это за пределами допустимого диапазона и поэтому нас порадуют сообщением:

The start value for sequence object ‘GenerateNumberSequence’ must be between the minimum and maximum value of the sequence object.

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

The sequence object ‘GenerateNumberSequence’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Опция CYCLE

Данная опция зацикливает последовательность и, достигнув максимального значения, последовательность продолжается с минимального. Например:

После того как максимальное значение было достигнуто, результаты станут такими:

Для выборки использовался запрос:

Если внимательно посмотреть на вывод, то можно заметить, что записи были перепутаны. Если бы мы не использовали последовательности, то вывод был бы

Но из-за того, что вторая запись пересекла диапазон допустим значений, номер был сброшен на минимальное значение, заданное для последовательности (10). Если сейчас посмотреть в каталог sys.sequences, то будет видно, что текущее значение равняется 10.

В следующий раз, заполнение таблицы могло бы быть таким:

В этот момент Sequence проверит порядок в котором записи будут вставлены и так как “Violet” идет раньше “Tape” и текущий номер равен 10, записи будут вставлены как:

Следующее_значение =Текущее_значение +Сдвиг т.е. 10 +1 будет присвоено для “Violet”. Теперь значение Sequence = 11 и для второй записи значение будет 12 следуя то же самой формуле.

Опция NO CYCLE

Sequence в сочетании с Over()

Можно использовать последовательность вместе с выражением Over для генерирования порядковых номеров как показано ниже:

Можно заметить, что записи были отсортированы и последовательность была применена верно к сохраненным данным. Это означает, что записи сначала сортируются и только потом применяется нумерация последовательности.

Ограничения использования Next Value для функций.

Функция sp_sequence_get_range

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

Вот что будет в результате выполнения:

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

Сравнение между Sequence и Identity

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SEQUENCES (AUTONUMBER) последовательность

В этом учебном материале вы узнаете, как создавать и удалять sequences (последовательности) в Oracle/PLSQL c синтаксисом и примерами.

Описание

В Oracle/PLSQL, вы можете создать автонумерацию с помощью последовательности. Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда вам нужно создать уникальный номер в качестве первичного ключа.

CREATE SEQUENCE

Синтаксис

sequence_name имя последовательности, которую вы хотите создать.

Пример

Таким образом, вы можете упростить CREATE SEQUENCE. Написав следующее:

DROP SEQUENCE

После того как вы создали последовательность в Oracle, вам можете понадобиться удалить её из базы данных.

Синтаксис:

sequence_name имя последовательности, которую вы хотите удалить.

Пример

Рассмотрим на примере, как удалить последовательность в Oracle.

ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ

Или вы могли бы создать такую же последовательность, но с опцией nocache :

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

Недостатком создания последовательности с cache, что если происходит отказ системы, все кэшированные значения последовательности, которые не были использованы, будут утеряны. Это приведет к разрывам в значениях, назначенной последовательности. Когда в система восстановится, Oracle будет кэшировать новые номера, с того места, где была прервана последовательность, игнорируя утерянные значения последовательности.

Примечание: Для восстановления утраченных значений последовательности, вы всегда можете выполнить команду ALTER SEQUENCE для сброса счетчика на правильное значение.
nocache означает, что ни одно из значений последовательности не хранятся в памяти. Эта опция может понизить производительность, однако, вы не должны столкнуться с разрывами в значениях, назначенной последовательности.

Вопрос: Как установить значение lastvalue в последовательность Oracle?

Ответ: Вы можете изменить lastvalue для последовательности Oracle, выполнив команду ALTER в последовательности.

Например, если последнее значение используемой последовательности Oracle был 100, и вы хотите, чтобы следующее значение было 225. Вы должны выполнить следующие команды.

Источник

CREATE SEQUENCE (Transact-SQL)

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

Сведения и сценарии использования функций CREATE SEQUENCE и NEXT VALUE FOR см. в разделе Порядковые номера.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

sequence_name
Указывает уникальное имя, под которым последовательность известна в базе данных. Тип sysname.

[ built_in_integer_type | user-defined_integer_type
Последовательность может быть определена с любым целочисленным типом. Допускаются следующие типы.

Если тип данных не указан, то по умолчанию используется тип bigint.

START WITH
Первое значение, возвращаемое объектом последовательности. Значение START должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением для нового объекта последовательности служит минимальное значение для объекта возрастающей последовательности и максимальное — для объекта убывающей.

INCREMENT BY
Значение, на которое увеличивается (или уменьшается, если оно отрицательное) значение объекта последовательности при каждом вызове функции NEXT VALUE FOR. Если значение приращения отрицательно, то объект последовательности убывает, в противном случае — возрастает. Приращение не может быть равно 0. По умолчанию для нового объекта последовательности используется приращение 1.

[ MINVALUE | NO MINVALUE ]
Указывает граничные значения для объекта последовательности. По умолчанию минимальным значением для нового объекта последовательности служит минимальное значение для типа данных объекта последовательности. Для типа данных tinyint это ноль, для всех остальных типов данных — отрицательное число.

[ MAXVALUE | NO MAXVALUE
Указывает граничные значения для объекта последовательности. По умолчанию максимальным значением для нового объекта последовательности служит максимальное значение для типа данных объекта последовательности.

[ CYCLE | NO CYCLE ]
Свойство, которое указывает, перезапускается объект последовательности с минимального значения (или максимального для объектов убывающих последовательностей) или вызывает исключение, когда достигнуто максимальное (или максимальное) значение. По умолчанию для новых объектов последовательности используется параметр цикличности NO CYCLE.

Циклическое повторение последовательности начинается не с начального, а с минимального или максимального значения.

[ CACHE [ ] | NO CACHE ]
Повышает производительность для приложений, использующих объекты последовательностей, сводя к минимуму число операций дискового ввода-вывода, которые требуются для создания порядковых номеров. По умолчанию имеет значение CACHE.

Например, если выбран размер кэша 50, то SQL Server не кэширует 50 отдельных значений. Кэшируется только текущее значение и число значений, оставшихся в кэше. Это значит, что объем памяти для хранения кэша всегда равен размеру двух экземпляров типа данных объекта последовательности.

Если параметр кэширования задан без указания размера кэша, то размер выбирается компонентом Database Engine. Однако пользователям не следует полагаться на предсказуемость выбора. Microsoft может изменить этот метод вычисления размера кэша без предварительного уведомления.

Если создание проводилось с параметром CACHE, то непредвиденное завершение работы (например, сбой электропитания) может привести к потере порядковых номеров, оставшихся в кэше.

Общие замечания

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

Управление кэшем

Для повышения производительности SQL Server заранее выделяет количество порядковых номеров, указанное в аргументе CACHE.

Например, новая последовательность создается с начальным значением 1 и размером кэша 15. Когда требуется первое значения, из памяти становятся доступными значения с 1 по 15. Последнее кэшированное значение (15) записывается в системные таблицы на диск. Когда используются все 15 номеров, то следующий запрос (для номера 16) вызывает повторное выделение кэша. Новое последнее кэшированное значение (30) записывается в системные таблицы.

Если компонент Компонент Database Engine останавливается после использования 22 номеров, то следующий порядковый номер, ожидающий в памяти (23), записывается в системные таблицы, заменяя ранее хранившийся номер.

После перезапуска SQL Server, когда требуется порядковый номер, считывается начальный номер из системных таблиц (23). В память выделяется кэш размером в 15 номеров (23–38), а следующий номер, не попавший в кэш (39), записывается в системные таблицы.

Если компонент Компонент Database Engine непредвиденно завершает работу (например, из-за сбоя электропитания), то последовательность перезапускается с номера, считываемого из системных таблиц (39). Все порядковые номера, выделенные в память (но не запрошенные пользователем или приложением), теряются. При такой обработке возможны пропуски в номерах, однако гарантируется, что одно значение ни в коем случае не будет дважды назначено одному объекту последовательности, если для нее не задан параметр CYCLE или не выполнен перезапуск вручную.

Кэш хранится в памяти путем отслеживания текущего значения (последнего назначенного) и количества значений, оставшихся в кэше. Таким образом, объем памяти, используемый для кэша, всегда равен размеру двух экземпляров типа данных объекта последовательности.

Если установить аргумент кэша в значение NO CACHE, то текущее значение последовательности будет записываться в системные таблицы при каждом использовании последовательности. Это может снизить производительность за счет увеличения числа обращений к диску, но снижает вероятность нежелательных пропусков номеров. Пропуски по-прежнему возможны, если номера запрашиваются с помощью функций NEXT VALUE FOR или sp_sequence_get_range, однако это означает, что пропущенные номера не используются либо используются в незафиксированных транзакциях.

Если в объекте последовательности используется параметр CACHE, то при перезапуске объекта последовательности или изменении свойств INCREMENT, CYCLE, MINVALUE, MAXVALUE или размера кэша кэш записывается в системные таблицы до выполнения изменения. Затем кэш перезагружается, начиная с текущего значения (номера не пропускаются). Изменение размера кэша вступает в силу немедленно.

Параметр CACHE при наличии кэшированных значений

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

Вычисляется следующее значение для объекта последовательности.

Новое текущее значение для объекта последовательности обновляется в памяти.

Вычисленное значение возвращается к вызывающей инструкции.

Параметр CACHE при пустом кэше

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

Вычисляется следующее значение для объекта последовательности.

Вычисляется последнее значение для нового кэша.

Строка системной таблицы для объекта последовательности блокируется, а значение, вычисленное на шаге 2 (последнее значение), записывается в системную таблицу. Создается событие Xevent cache-exhausted, чтобы сообщить пользователю о новом сохраненном значении.

Параметр NO CACHE

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

Вычисляется следующее значение для объекта последовательности.

Новое текущее значение для объекта последовательности записывается в системную таблицу.

Вычисленное значение возвращается к вызывающей инструкции.

Метаданные

Чтобы получить сведения о последовательностях, запросите представление sys.sequences.

Безопасность

Разрешения

Необходимо разрешение CREATE SEQUENCE, ALTER или CONTROL для схемы SCHEMA.

Члены предопределенных ролей базы данных db_owner и db_ddladmin могут создавать, изменять и удалять объекты последовательности.

Члены предопределенных ролей базы данных db_owner и db_datawriter могут обновлять объекты последовательности, вызывая создание номеров.

В следующем примере пользователю AdventureWorks\Larry предоставляется разрешение на создание последовательностей в схеме Test.

Владение объектом последовательности может быть передано с помощью инструкции ALTER AUTHORIZATION.

Если в последовательности используется определяемый пользователем тип данных, то создатель последовательности должен иметь разрешение REFERENCES для этого типа.

Аудит

Для аудита инструкции CREATE SEQUENCE отслеживайте SCHEMA_OBJECT_CHANGE_GROUP.

Примеры

Примеры создания последовательностей и использования функции NEXT VALUE FOR для формирования порядковых номеров см. в разделе Порядковые номера.

В большинстве из следующих примеров объекты последовательности создаются в схеме с именем Test.

Чтобы создать схему Test, выполните следующую инструкцию.

A. Создание последовательности, увеличивающейся на 1

В следующем примере пользователь Thierry создает последовательность с именем CountBy1, которая увеличивается на единицу при каждом использовании.

Б. Создание последовательности, уменьшающейся на 1

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

В. Создание последовательности, увеличивающейся на 5

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

Г. Создание последовательности, начинающейся с заданного числа

После импорта таблицы Thierry замечает, что максимальный номер идентификатора составляет 24 328. Thierry требуется последовательность, которая будет создавать номера, начиная с 24 329. В следующем коде создается последовательность, начинающаяся с 24 329 и увеличивающаяся на 1.

Д. Создание последовательности со значениями по умолчанию

В следующем примере создается последовательность со значениями по умолчанию.

Чтобы просмотреть свойства последовательности, выполните следующую инструкцию.

Частичный перечень выходных данных демонстрирует значения по умолчанию.

Е. Создание последовательности с заданным типом данных

В следующем примере создается последовательность с типом данных smallint и диапазоном значений от –32 768 до 32 767.

Ж. Создание последовательности с использованием всех аргументов

В следующем примере создается последовательность с именем DecSeq, использующая тип данных decimal и диапазон от 0 до 255. Последовательность начинается со 125 и увеличивается на 25 при каждом создании номера. Поскольку для последовательности настроено циклическое повторение при превышении максимального значения 200, она перезапускается с минимального значения 100.

Чтобы просмотреть первое значение, выполните следующую инструкцию. Параметр START WITH равен 125.

Выполните инструкцию еще три раза, чтобы вернуть значения 150, 175 и 200.

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

Источник