MS SQL 2011 (Denali) – Sequence

Sequence (NEW) — последовательность

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

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

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

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

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

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

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

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

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

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

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

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

Sequence может быть следующих типов:

  • Int
  • Smallint
  • Tinyint
  • Bigint
  • Decimal
  • Numeric

Не обязательно начинать последовательность с единицы. Можно начинать с любого числа в пределах возможных значений объявленного типа. Например, для целочисленных значений это может быть от -2147483648 до 2147483647.

Проверим на практике, что скажет 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.

Для решения проблемы есть два пути:

  • Использовать служебные слова Restart или Restart With.
  • Использовать опцию CYCLE

Опция CYCLE

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

 

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

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

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

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

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

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

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

Опция NO CYCLE

Поведение такой опции уже рассматривалось в самом начале, и является значением по умолчанию при создании Sequence.

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

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

Результат:

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

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

Sequence ни в каких случаях нельзя использовать в сочетании с:

  • Проверкой ограничений (constraints)
  • Значениями по умолчанию
  • Вычисляемыми колонками
  • Представлениями (views)
  • Пользовательскими функциями
  • Пользовательскими функциями агрегации
  • Подзапросами
  • СТЕ (Common Table Expression)
  • Подтаблицами
  • Выражением TOP
  • Выражением Over
  • Выражением  Output
  • Выражением On
  • Выражением Where
  • Выражением Group By
  • Выражением Having
  • Выражением Order By
  • Выражением Compute
  • Выражением Compute By

Функция sp_sequence_get_range

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

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

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

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

Не стоит ставить между ними глобальный знак равенства из-за следующих факторов:

  • Identity относится к таблице и является ее частью неотделимой, Sequence – независимый объект базы данных.
  • Можно получить набор последовательности с помощью sp_sequence_get_range, что в принципе невозможно с Identity.
  • Для Sequence можно определять границы значений, что так же невозможно для Identity.
  • Цикличность значений можно задать так же только для Sequence.

И еще несколько слов про Sequence.

  • Sequence дает больший прирост производительности по сравнению с Identity. Сравнение и результаты в статье Аарона Бертарнда (Aaron Bertrand)
  • Можно задавать права доступа к Sequence, так же как и к другим объектам базы.

Почитать дополнительно о Sequence можно на MSDN:

  1. CREATE SEQUENCE
  2. Creating and Using Sequence Numbers
  3. sp_sequence_get_range

 

Дальше будет так же интересно, так что оставайтесь на связи. Перевод.

Hard’n’heavy!

 

 

MS SQL 2011 (Denali) — SSMS

Одна из наиболее интересных и захватывающих разработок от Майкрософт в технологическом плане была представлена 8 ноября 2010 года. В этот день состоялся релиз CTP 1 SQL Server 2011 (Codename Denali). CTP доступна как в х86, так и в х64.

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

За последние несколько лет Майкрософт внедрила много интересных технологий, которые были приняты разработчиками. Самые значительные изменения были сделаны в 2005 SQL сервере и получили дополнительное развитие в 2008 выпуске.

В этой статье будут рассмотрены изменения и новые возможности которые произошли в новой версии SQL Server в языке TSQL, администрировании и анализа данных.

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

Далее пойдет речь о новшествах в SQL Server Management Studio (SSMS)

Подробнее

SQL Change Master — II

Работа в консольном режиме

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

Changecontroller.exe alias=dev log=”c:\temp\log.txt” showlog

Данная запись говорит, что надо найти и запустить обновление для окружения dev, лог выполнения записать в файл log.txt в папке c:\temp и после выполнения обновления показать лог. На мой взгляд, все достаточно просто и применимо в различных ситуациях, как для Continuous Integration, так и для работы в студии. Можно и просто вставлять в батнички для обновления многих настроек окружения.

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

Changecontroller.exe alias=dev base=testbasea

Данная запись говорит, что надо найти обновление для окружения dev, для базы testbasea и запустить скрипты обновлений.

Если вы что-то указали неверно, или же программа не может найти указанное окружение или базу данных, то SQL Change Master запуститься в графическом режиме.

Работа в студии

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

Итак, для работы нам понадобится SQL Change Master и датабазный проект от МS.

Настройка датабазного проекта

Создаем новый проект. File > New Project…

В появившемся окне выбираем шаблоны для баз данных для SQL Server и в них находим, к примеру, SQL Server 2008 Database Project.

Обратите внимание, что название проекта должно в точности совпадать с названием базы данных, которое было указано в SQL Change Master.

Подробнее

SQL Change Master — I

Из истории вопроса

Прежде чем приступить к описанию программы, я бы хотел осветить сам вопрос работы с базой данных по гибким методологиям. Или лучше сказать итеративное развитие базы данных, когда изменения в базу вносятся тогда, когда они нужны. Когда база развивается одновременно с приложением и, по сути, идет модель Code First, тогда база отвечает потребностям кода, а не код старается подстроиться под базу.

Я думаю, что для большинства, вполне очевидно, что модель хранения данных, и модель работы с данными, это две слабо пересекающиеся модели. Еще было бы замечательно поделить это все на модели/структуры для чтения и модели/структуры для  записи. Но это отдельная огромная тема, которую я не готов раскрыть.

Итак, в начальных условиях у нас в идеале новый проект, green field, или как минимум еще не выпущенный в релиз (разработчики любят начинать все с нуля). Но ничто по большому счету не мешает применить практики итеративного обновления данных и для выпущенного проекта. Если вы используете у себя итеративную поставку, то почему база должна отличаться?

На тему подходов к разработке БД и хранению ее скриптов в системе контроля версий сломано немало копий. Многие крупные разработчики предлагают свои решения, как программные, так и организаторского плана. Хороший обзор получился у  Outcoldman’а, описываются основные подходы к решению обновления БД и хранению истории с помощью проектов от Microsoft, Red Gate, еще каких-то разработчиков. Есть у всех свои слабые и сильные стороны. Я, так же как и Outcoldman, придерживаюсь мнения, что надо писать собственные скрипты обновления и использовать либо готовые решения, либо самому написать простейшие управляющие вещи, по выполнению этих скриптов.

Я лично пробовал только итеративный подход с ручным написанием/генерацией скриптов и датабазные проекты от Микрософт в студии. Датабазные проекты меня не впечатлили, так как там отсутствуют очень важные в жизни вещи, как-то:

  • Миграция данных
  • Переименование через drop\create
  • Атомарность обновлений
  • Однозначное определение текущего состояния базы

Не секрет, что некоторые технологий от Микрософта направлены на создание вау-эффекта и годны только для демонстрации и создания простейших приложений не из области энтерпрайз. Я думаю, что датабазные проекты с генерацией скриптов разницы совсем не то, что необходимо в большинстве случаев.

Подробнее

SQL Changes Master

В данном видео (подкатом) я рассказываю о программе для итеративного обновления баз данных.

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

Подробнее