Создание базы данных

Все БД, управляемые сервером Microsoft SQL Server 2008, можно разбить на две группы: системные и пользовательские. Системные БД создаются при инсталляции сервера и используются сервером в процессе его работы. Особую роль среди них играет БД MASTER, хранящая информацию обо всех остальных БД, управляемых сервером.

Для создания пользовательской БД применяется оператор CREATE DATABASE, который должен выполняться в контексте системной БД MASTER. В простейшем случае создание БД выглядит так, как это представлено на рис. 4.1.

Рис. 4.1. Пример создания базы данных

С помощью кода на рис. 4.1 создается база данных с именем BSTU. БД представляет собой набор файлов операционной системы трех типов: первичный файл (расширение mdf), вторичные файлы (ndf) и файлы журнала транзакций (log).

Все файлы БД, кроме файлов журнала транзакций, распределены по файловым группам. Файловые группы – это поименованный набор файлов БД. Журнал транзакций в операторе CREATE DATABASE описывается отдельно в секции, обозначенной ключевыми словами LOG ON. Обязательной является файловая группа, называемая первичной. Для ее обозначения используются ключевые слова ON PRIMARY.

Описание каждого файла БД включает несколько атрибутов: логическое имя (заданное ключевым словом name), файл операционной системы (filename), а также первоначальный размер (size), максимальный размер (maxsize), приращение размера (filegrowth), заданное абсолютной величиной или в процентах по отношению к первоначальному размеру. Обязательными являются только первичный файл БД, всегда расположенный в первичной файловой группе, и файл журнала транзакций.

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

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

Разработчик БД помимо первичной файловой группы может создавать дополнительные файловые группы, называемые вторичными. Во вторичных файловых группах могут располагаться только вторичные файлы. В первичной файловой группе помимо обязательного первичного файла тоже могут быть расположены вторичные файлы (рис. 4.2).

Рис. 4.2. Пример создания базы данных, использующей вторичную группу
 и вторичные файлы

На рис. 4.2 приведен пример оператора CREATE DATABASE, включающего пять файлов: один первичный, три вторичных файла и один файл журнала транзакций. Файлы (кроме файла журнала транзакций) распределены по двум файловым группам: в первичной файловой группе находятся один первичный и один вторичный файлы; во вторичной файловой группе с именем FG1 – два вторичных файла.

Файловые группы используются в основном для упрощения администрирования БД. Существуют операторы (например, копирования и восстановления БД), позволяющие рассматривать файловую группу как единое целое и выполнять операции не для каждого файла по отдельности, а сразу для файловой группы. Кроме того, файловые группы применяются при секционировании таблиц – технологии, позволяющей разместить фрагменты таблицы в разных файловых группах, что при некоторых дополнительных условиях позволяет повысить производительность запросов к этой таблице.

При создании таблиц и индексов дисковая память для них автоматически отводится в файловой группе по умолчанию. Для размещения этих объектов БД в другой файловой группе следует явно указывать ее имя в операторе CREATE, создающем таблицу или индекс. Кроме того, с помощью оператора ALTER DATABASE можно сделать другую файловую группу, которая будет использоваться по умолчанию (рис. 4.3).

Рис. 4.3. Назначение вторичной файловой группы FG1
файловой группой по умолчанию

На рис. 4.3 приведен пример использования оператора ALTER DATABASE, позволяющего назначить для БД BSTU файловой группой по умолчанию вторичную группу с именем FG1.

Возможности оператора CREATE DATABASE значительно шире, чем описаны в этом пособии. Для знакомства с их полным перечнем рекомендуется воспользоваться пособием [5].

Изменение базы данных

Изменить существующую БД можно с помощью оператора ALTER DATABASE.

Установка параметров базы данных. БД характеризуется не только структурой ее файлов, определяемой при ее создании с помощью оператора CREATE DATABASE, но и набором параметров. Начальные значения (по умолчанию) параметров устанавливаются при создании БД. Изменить их значения можно с помощью оператора ALTER DATABASE SET. Все параметры можно разбить на несколько групп: параметры автоматических действий, параметры курсора, параметры внешних действий, параметры восстановления, параметры SQL, параметры моментальных снимков, параметры компонента Service Broker и параметры доступности БД.

На рис. 4.4 представлен пример установки (изменения значений, установленных ранее) некоторых параметров, относящихся к группе параметров автоматических действий.

Рис. 4.4. Установка параметров группы автоматических действий

Каждый из устанавливаемых параметров может принимать только два значения ON (включен) и OFF (выключен).

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

Параметр AUTO_CREATE_STATISTICS позволяет включить или отключить автоматический сбор статистики. В процессе своей работы сервер собирает статистику, которую использует специальная компонента сервера – оптимизатор запросов. Собранная сервером статистика позволяет оптимизатору запросов принимать решения о способах выполнения SQL-запросов к БД.

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

С помощью встроенной функции DATABASEPROPERTYEX всегда можно получить текущие установки параметров БД. На рис. 4.5 приведен пример использования функции DATABASEPROPERTYEX в SELECT-запросе, а также результат выполнения этого запроса.

Рис. 4.5. Отображение текущих значений параметров БД с помощью
встроенной функции DATABASEPROPERTYEX

Функция DATABASEPROPERTYEX может возвращать два значения: 1 (соответствует включенному состоянию) и 0 (выключенное состояние). В SELECT-запросе используется оператор CASE, который будет подробно рассматриваться позже. Здесь же поясним: с его помощью в результате запроса, представленного на рис. 4.5, осуществляется замена числа 1 на строку ON, а числа 0 на OFF.

Для изучения параметров рекомендуется пособие [5].