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

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 будет упомянуто позднее.

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

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

Полный синтаксис для создания сиквенса

CREATE SEQUENCE [schema.]sequencename

[INCREMENT BY number]

[START WITH number]

[MAXVALUE number | NOMAXVALUE]

[MINVALUE number | NOMINVALUE]

[CACHE number | NOCACHE]

Создание сиквенса может быть очень простым. Например сиквенс использованный на рисунке 7-6 был создан с помощью команды

create sequence seq1;

Список доступных параметров

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

Кеширование значений критично для производительности. Выборка из сиквенса может осуществляться только одной сессией в момент времени. Механизм генерации значений очент эффективный: он гораздо быстрее чем блокировка строки, обновление строки или управление транзакцией. Но даже несмотря на это, выборка из сиквенса может быть точкой конкуренции сессий. Директива CACHE позволяет Oracle генерировать номера блоками. Пред-сгенерированные значения выбираются быстрее чем генерация по запросу.

The default number of values to cache is only 20. Experience shows that this is usually not enough. If your application selects from the sequence 10 times a second, then set the cache value to 50 thousand. Don’t be shy about this

Создание поля автоинкремента (счетчика) в таблице базы данных MS SQL Server, которая размещается в «*.mdf»-файле

В данной теме показано как создать поле-счетчик (уникальное поле) в таблице базы данных Microsoft SQL Server , которая размещается в файле «MyDataBase.mdf» .

Данная тема базируется на предыдущих темах:

Поиск на других ресурсах:

Содержание

Условие задачи

Заданы файлы «MyDatabase.mdf» и «MyDataBase.ldf» базы данных, которые предназначены для работы в системе управления реляционными базами данных Microsoft SQL Server . Файлы можно скачать в архиве здесь .

База данных содержит одну таблицу с именем Source . Таблица Source содержит следующие поля:

• ID_Source – целого типа ( int );
• Name – типа «строка» из 50 символов;
• Address – типа строка из 100 символов.

В задаче нужно сделать поле ID_Source уникальным счетчиком. При добавлении новой записи в таблицу, значение поля должно увеличиваться на 1 (автоинкрементное поле), то есть быть уникальным.

Выполнение

1. Подключение «*.mdf» -файла базы данных в MS Visual Studio

Запустить на выполнение MS Visual Studio .

С помощью утилиты Server Explorer нужно подключить ранее созданніе файлы «MyDataBase.mdf» и «MyDataBase.ldf» . Архив с файлами можно загрузить здесь .

Рекомендуется, чтобы файлы были размещены в одном каталоге.
Пример добавления готового (ранее созданного) «*.mdf» -файла локальной базы данных к MS Visual Studio подробно описывается в статье:

После подключения базы данных в окне Server Explorer будет отображена база данных «MyDataBase.mdf» (рисунок 1).

База данных содержит одну таблицу Source (рисунок 2), которая содержит поля в соответствии с условием задачи.

Рис. 1. База данных MyDataBase.mdf в окне Server Explorer

Рис. 2. Таблица Source

2. Настройка поля ID_Source как счетчика. Свойство «Identity Column»

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

Чтобы установить поле ID_Source уникальным, нужно выполнить следующие действия:

• раскрыть вкладку Tables в базе данных MyDataBase.mdf (рисунок 3);
• в вкладке Tables сделать правый клик «мышкой» и из контекстного меню вызвать команду «Open Table Definition» (рисунок 3). В результате откроется окно определения полей таблицы;
• активировать поле ID_Source и в окне свойств «Properties» установить значение свойства «Identity Column = ID_Source» (рисунок 4);
• сохранить и закрыть таблицу Source .

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление свойства Identity Column в значение ID_Source

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

Теперь эту таблицу можно использовать в своих проектах.

3. Заполнение таблицы данными

После установлки в таблице поля ID_Source как уникального счетчика можно программно или вручную заполнять таблицу данными (записями).

Чтобы заполнить таблицу данными (записями) нужно выполнить следующие действия:

• вызвать контекстное меню с помощью правого клика мышкой на таблице Source и из контекстного меню выбрать команду «Show Table Data» (рисунок 5). В результате откроется окно ввода данных в таблицу (рисунок 6);
• ввести данные в таблицу Source . Поскольку, поле ID_Source есть счетчиком, то вводить данные в это поле не удастся. Можно вводить данные только в полях Name и Address . Значение поля Source будет генерироваться автоматически.

Рис. 5. Команда «Show Table Data»

Рис. 6. Ввод данных в таблицу Source

Подключение сбора данных к SQL Server

Для того чтобы подключить сбор данных показателей в базу данных на SQL Server необходимо:

1. Настроить сборщик данных аналогично тому как это сделано в статье «Настройка системного монитора для контроля производительности Windows и MS SQL Server»
2. Создать отдельную базу данных в СУБД для сбора показателей производительности
3. Создать источник данных ODBC для записи показателей
4. Настроить ранее созданный сборщик данных на запись в SQL Server
5. Запустить/перезапустить сборщик данных

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

Создание источника данных ODBC

Первым делом необходимо запустить утилиту «Источники данных (ODBC)» (Data sources (ODBC)), для этого можно перейти в «Панель управления» (Control panel) → «Администрирование» (Administrative tools) → «Источники данных (ODBC)» (Data sources (ODBC)).

Источники данных (ODBC). Системный DSN

На вкладке «Системный DSN» (System DSN) необходимо нажать «Добавить» (Add)
Далее, в открывшемся окне создания нового источника, необходимо выбрать «SQL Server» и нажать «Готово»

Создание нового источника ODBC

В следующем окне необходимо задать имя источника, его описание и, самое важное, экземпляр SQL Server в котором находится база-получатель. Таким образом, запись данных можно производить в том числе на удаленную, а не только локальную, машину. Для этого экземпляр SQL Server должен быть корректно настроен на обнаружение в сети.

Создание нового источника ODBC. Ввод имени, описания, экземпляра SQL Server

Перейдем к следующему шагу, на котором необходимо указать способ аутентификации. Здесь важно установить «проверку подлинности учетной записи Windows NT», поскольку в ином случае у меня были проблемы с авторизацией. Используемая по умолчанию сетевая библиотека — TCP/IP с динамическим определением порта, что нам подходит, главное, убедитесь что в настройках сервера данный протокол включен.

Создание нового источника ODBC. Настройка аутентификации

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

Создание нового источника ODBC. Выбор базы данных

Далее возможно указать различные параметры (такие как язык системных сообщений SQL-сервера, шифрование и прочее), которые, в общем случае, можно оставить по умолчанию. Поэтому я опущу данный шаг. После чего необходимо нажать «Готово».

В следующем окне будет приведена информация о создаваемом источнике данных и возможность проверки источника данных, которую я рекомендую выполнить. Если все сделано правильно, проверка должна сообщить «ТЕСТ УСПЕШНО ЗАВЕРШЕН» («TESTS COMPLETED SUCCESSFULLY!»)

Создание нового источника ODBC. Описание источника и его проверка

Мы сделали самое важное — создали наш источник данных по которому будем подключаться в базе данных!

Настройка ранее созданного сборщика данных

Откроем системный монитор (performance monitor) и перейдем к ранее созданному сборщику данных (или создадим и настроим новый, если его пока не существует).
Первым делом нам необходимо открыть свойства нашего сборщика данных и установить:

• «Формат журнала» (Log format) в значение «SQL»
• Выбрать источник данных (Data source) — ранее созданный нами источник данных ODBC

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

Установка формата журнала сборщика данных

Вторая важная настройка — задать пользователя из-под которого происходит выполнение. По умолчанию запуск происходит от имени системной записи «SYSTEM», нам же необходимо запустить задание из-под пользователя, который имеет права на SQL Server (если запись происходит на локальном экземпляре и у системной учетной записи есть права на SQL Server — этот шаг можно пропустить). Данная настройка выполняется в свойствах группы сборщиков.

Изменение пользователя в свойствах группы сборщиков

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

Если подключение было выполнено корректно, в базе данных будут созданы 3 таблицы, которые содержат всю необходимую информацию:

• DisplayToID — содержит информацию и сборщике данных
• CounterDetails — содержит информацию о счетчиках
• CounterData — содержит значения счетчиков

Далее, эту информацию можно использовать любым способом, который Вам придет на ум. Некоторые примеры такого использования приведены в статье «Использование данных, полученных сборщиком данных и записанных в SQL Server»

Синтаксис ALTER TABLE на примере MS SQL Server

Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:

Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.

Добавляем новый столбец

Для примера добавим новый column Address в таблицу Customers:

В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:

Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».

Удаляем столбец

Теперь можно удалить column Address:

Меняем тип

Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).

Добавляем ограничения CHECK

Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.

При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:

По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.

Добавляем внешний ключ

Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:

Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):

Добавляем первичный ключ

Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:

Добавляем ограничения с именами

Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):

Удаляем ограничения

Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:

Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.

Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:

Хотите знать про SQL Server больше? Добро пожаловать на курс «MS SQL Server Developer» в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:

Как работать с Data Collector

Убедитесь, что SQL Server Integration Services установлен, а SQL Server Agent, Management Data Warehouse и Data Collection включены.

1. В Object Explorer среды SQL Server Management Studio раскройте папку Management.
2. В контекстном меню Data Collection выберите Configure Management Data Warehouse.

Укажите Set up data collection.

Нажмите далее (Next).

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

Нажмите Next, проверьте все параметры и затем Finish.

Data Collection имеет три предустановленных набора мониторинга в папке System Data Collection Sets (Object Explorer -> Management -> Data Collection): Disk Usage, Query Statistics и Server Activity. Кроме того, они имеют встроенные отчёты.

Набор Disk Usage показывает информацию по файлам данных (MDF и NDF) и файлам лога транзакций (LDF). Статистику ввода/вывода.

В контекстном меню Data Collection имеется отчёт Disk Usage built-in, который показывает размер файлов, их прирост, в том числе и ежедневный.

Набор Query Statistics показывает статистику, активность и планы 10 самых «тяжёлых» запросов.

Набор Server Activity показывает общую нагрузку на процессор, память, сеть и дисковую подсистему. В отчётах можно увидеть активность экземпляра SQL Server и операционной системы, ЦПУ, память, сеть, вводвывод.

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

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

Функционал не поддерживается в версиях SQL Server ниже 2008-ого. И присутствует только в редакциях Enterprise, Standard, Business Intelligence, и Web.

В отличие от Activity Monitor, нет возможности просматривать графики в реальном времени, но собранная информация может храниться на протяжении нескольких дней. В базовом наборе представлены только основные показатели, а для расширения необходимы знания средств разработки.