3.4. Создание и удаление базы данных


В этом разделе мы рассмотрим всевозможные способы создания пользовательских баз данных при применении как оператора Transact-SQL CREATE DATABASE, так и диалоговых средств Management Studio. Кроме создания баз данных мы научимся их удалять при помощи простого оператора DROP DATABASE, а также при использовании диалоговых средств Management Studio.
Для отображения характеристик и состояния существующей базы данных средствами Transact-SQL можно использовать описанные системные представления и диалоговые средства, существующие в Management Studio. Мы рассмотрим все подходящие варианты.

3.4.1. Использование операторов Transact-SQL для создания, отображения и удаления баз данных

3.4.1.1. Оператор создания базы данных

Для создания новой базы данных используется оператор CREATE DATABASE. Его синтаксис (только для целей создания новой базы данных) показан в листинге 3.1 и на графе 3.1. Детализация синтаксических конструкций представлена в графах 3.2—3.7. Основные синтаксические конструкции, как мы ранее договорились, будем представлять и в нотациях Бэкуса — Наура, и при помощи R-графов.

Листинг 3.1. Синтаксис оператора CREATE DATABASE.

Вариант создания новой базы данных

CREATE DATABASE <логическое имя базы данных>
[ CONTAINMENT = { NONE | PARTIAL } ]
[ <предложение ON> [ <предложение LOG ON> ] ]
[ COLLATE <порядок сортировки> ]
[ WITH <опция> [, <опция>]... ];
<предложение ON> ::=
ON [ PRIMARY ] <спецификация файла> [, <спецификация файла>]...
[, <файловая группа> [, <файловая группа>] ...]
<предложение LOG ON> ::=
LOG ON <спецификация файла> [, <спецификация файла> ]...
<опция> ::=
{ FILESTREAM (<опция файлового потока> [, <опция файлового потока> ]...)
| DEFAULT_FULLTEXT_LANGUAGE =
{ <код языка> | <название языка> | <псевдоним языка> }
| DEFAULT_LANGUAGE =
{ <код языка> | <название языка> | <псевдоним языка> }
| NESTED_TRIGGERS = { OFF | ON }
| TRANSFORM_NOISE_WORDS = { OFF | ON}
| TWO_DIGIT_YEAR_CUTOFF = <год между 1753 и 9999>
| DB_CHAINING { OFF | ON }
| TRUSTWORTHY { OFF | ON }
}
<опция файлового потока> ::=
{ NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
| DIRECTORY_NAME = '<имя каталога>'
}


ЗАМЕЧАНИЕ
Оператор CREATE DATABASE с несколько измененным синтаксисом может также использоваться и для некоторых иных целей: присоединения где-то кем-то когда-то созданной базы данных, возможно, на другом компьютере к списку баз данных текущего экземпляра сервера, а также для создания так называемого мгновенного снимка базы данных (SNAPSHOT). Все это мы рассмотрим чуть позже в данной главе.

В результате успешного создания новой базы данных будут созданы все специфицированные в операторе или сформированные по умолчанию с соответствующими характеристиками файлы базы данных. Самой базе данных будут присвоены заданные явно или по умолчанию значения ее характеристик. Файлы данных могут быть объединены в файловые группы.

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

ЗАМЕЧАНИЕ ПО СИНТАКСИСУ
Во множестве предыдущих версий документации по SQL Server сообщалось, что начиная с данной версии все операторы Transact-SQL должны завершаться символом точка с запятой (;). Это не очень соответствовало реальной действительности. И тогда операторы можно было не завершать этим символом. Лучшим вариантом будет, если вы всегда станете заканчивать любой оператор Transact-SQL символом точка с запятой.

Логическое имя базы данных

Логическое имя базы данных является обязательным параметром. Оно идентифицирует создаваемую базу данных. Имя должно соответствовать правилам задания идентификаторов, обычных или с разделителями (см. главу 2). В обычном идентификаторе здесь также можно использовать и буквы кириллицы, если при инсталляции сервера вы задали порядок сортировки Cyrillic_General_CI_AS. В идентификаторах с разделителями, как вы помните, можно использовать любые символы. Такой идентификатор только нужно заключить в квадратные скобки. Имя не должно содержать более 128 символов. Имя должно быть уникальным среди имен всех баз данных текущего экземпляра SQL Server. По этому имени вы обращаетесь к конкретной базе данных экземпляра сервера во всех операторах Transact-SQL, где требуется указать базу данных. Это единственный обязательный параметр в данном операторе.

Предложение CONTAINMENT

[ CONTAINMENT = { NONE | PARTIAL } ]

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

Если же указано значение PARTIAL, то создается так называемая "partially contained" база данных, т. е. частично автономная. Пользователь может подключаться к такой базе данных, используя отдельные средства аутентификации, не связанные с уровнем экземпляра сервера. Такую базу данных проще перенести в другой экземпляр сервера базы данных, на другой компьютер. Кроме того, в таких базах данных можно избежать неприятностей, которые иногда возникают при создании временных таблиц, где присутствуют строковые столбцы с порядком сортировки, отличным от принятого по умолчанию.
Подобные базы данных мы позже рассмотрим в примерах.

Предложение ON

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

О вторичных файловых группах см. чуть дальше.

Все описания файлов заключаются в круглые скобки (см. далее синтаксис спецификации файла в разд. "Спецификация файла") и отделяются друг от друга запятыми.

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

Предложение LOG ON

Необязательное предложение LOG ON описывает файл (файлы) журнала транзакций. 

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

Задание нескольких файлов журналов транзакций имеет смысл только в том случае, если для одного файла не хватает места на внешнем носителе. Тогда администратор БД создает файл (файлы) на другом носителе (других носителях).

ЗАМЕЧАНИЕ
Как видно из приведенного синтаксиса оператора CREATE DATABASE, при создании базы данных можно вообще не задавать никаких файлов данных и файлов журнала транзакций (эти конструкции заключены в описании синтаксиса в обрамляющие квадратные скобки). Если приглядеться внимательно к этому описанию, то можно увидеть, что допустим вариант задания файла (файлов) данных при отсутствии задания файлов журнала транзакций. Однако указать файл (файлы) журнала транзакций при отсутствии описания файла данных нельзя. В таком случае вы получите ошибку системы.
Если при создании новой базы данных вы не укажете файл данных или файл журнала транзакций, то система всем характеристикам этих файлов присвоит значения по умолчанию (см. далее).

Предложение COLLATE

Необязательное предложение COLLATE позволяет задать для создаваемой базы данных порядок сортировки, отличный от того, который был установлен при инсталляции системы для экземпляра сервера. Если предложение не указано, то база данных будет иметь порядок сортировки по умолчанию, заданный при инсталляции системы. Мы с вами при инсталляции SQL Server установили Cyrillic_General_CI_AS.

Предложение WITH

Необязательное предложение WITH позволяет описать некоторые дополнительные характеристики создаваемой базы данных.

FILESTREAM (<опция файлового потока> [, <опция файлового потока> ]...)
<опция файлового потока> ::= { NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL } | DIRECTORY_NAME = '<имя каталога>' }

После ключевого слова FILESTREAM в скобках перечисляются опции файлового потока.

  • Опция NON_TRANSACTED_ACCESS определяет возможность доступа к данным файлового потока вне контекста транзакций. Транзакции мы рассмотрим в главе 10. Значениями опции являются:

  • OFF — доступ к данным вне транзакции недопустим;
  • READ_ONLY — к данным файлового потока возможен доступ вне транзакций толь-
  • ко для операций чтения;
  • FULL — допустимы все операции к данным файлового потока вне транзакций.

  • Опция DIRECTORY_NAME задает имя каталога. Это имя должно быть уникальным среди имен каталогов, заданных параметром DIRECTORY_NAME текущего экземпляра сервера. Каталог с этим именем будет создан внутри сетевого каталога экземпляра сервера базы данных. Используется для файловых таблиц (FileTable), которые мы рассмотрим в главе 5.

  • Опция DEFAULT_FULLTEXT_LANGUAGE (допустимо только для автономной базы данных) задает язык базы данных по умолчанию для полнотекстового поиска в индексированных столбцах. Язык может задаваться в виде кода языка, его названия или псевдонима. Список допустимых языков см. в приложении 5.

  • Опция DEFAULT_LANGUAGE (допустимо только для автономной базы данных) задает язык по умолчанию для вновь создаваемых регистрационных имен пользователей.

  • Может задаваться в виде кода языка, его названия или псевдонима.

  • Опция NESTED_TRIGGERS (допустимо только для автономной базы данных) задает возможность использования вложенных триггеров AFTER. Если указано OFF, вложенные триггеры недопустимы. При задании ON может существовать до 32 уровней триггеров. То есть триггер может вызывать (разумеется, неявно) другой триггер, который, в свою очередь, инициирует обращение к триггеру следующего уровня.
  • И так до 32 уровней.

  • Опция TRANSFORM_NOISE_WORDS (допустимо только для автономной базы данных) задает поведение сервера базы данных в некоторых ситуациях полнотекстового поиска в базе данных. Существует понятие noise word или stop word. Это слова, которые слишком часто присутствуют в текстах и не имеют особого смысла при выполнении поисковых действий. Чаще всего это предлоги, для некоторых языков артикли, а также множество других часто встречающихся в языке слов.

  • Значение OFF (по умолчанию) приводит к тому, что если в запросе встречаются такие слова и запрос возвращает нулевое количество строк, то просто выдается предупреждающее сообщение.

    Если указано ON, то система выполнит преобразование запроса, удалив из него соответствующие слова.

  • Опция TWO_DIGIT_YEAR_CUTOFF (допустимо только для автономной базы данных) задает значение года в диапазоне между 1753 и 9999. По умолчанию принимается 2049. Это число используется для интерпретации года, заданного двумя символами.

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

  • Опция DB_CHAINING определяет, может ли создаваемая база данных использоваться в цепочках связей между несколькими базами данных. OFF (по умолчанию) запрещает такое использование, ON — разрешает.

  • Опция TRUSTWORTHY задает, могут ли программные компоненты базы данных (хранимые процедуры, представления, созданные пользователем функции) обращаться к ресурсам вне базы данных. OFF (по умолчанию) запрещает обращение к внешним ресурсам, ON — разрешает.

  • Спецификация файла

    Синтаксис спецификации файла, одинаковый как для файлов данных, так и для журналов транзакций, показан в листинге 3.2 и в соответствующем R-графе (граф 3.6).

    Листинг 3.2. Синтаксис спецификации файла

    <спецификация файла> ::= ( NAME = <логическое имя файла>, FILENAME = { '<путь к файлу>' | '<путь к файловому потоку>' } [, SIZE = <целое1> [ KB | MB | GB | TB ] ] [, MAXSIZE = { <целое2> [ KB | MB | GB | TB ] | UNLIMITED } ] [, FILEGROWTH = <целое3> [ KB | MB | GB | TB | % ] ] )


    В спецификации файла обязательными являются только предложения NAME (логическое имя файла) и FILENAME (имя файла в операционной системе). В случае отсутствия любого из предложений SIZE, MAXSIZE или FILEGROWTH соответствующим характеристикам будут присвоены значения по умолчанию, которые мы рассмотрим далее. Эти значения по умолчанию различны для файлов данных и для файлов журнала транзакций.

    В R-графе указаны элементы "единица измерения" и "единица измерения2". Второй элемент отличается от первого тем, что в его списке присутствует и знак процента.

    Предложение NAME

    NAME = <логическое имя файла>

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

    Если ни один файл данных явно при создании базы данных не описывается (отсутствует предложение ON), то логическому имени единственного файла данных присваивается имя самой базы данных. Например, если создаваемая база данных имеет имя Strange, то и логическое имя файла данных по умолчанию будет Strange.

    Если не задается ни одного файла журнала транзакций (не указано предложение LOG ON), то логическому имени единственного файла журнала транзакций присваивается имя, состоящее из имени базы данных, к которому добавляется суффикс _log. Для той же базы данных Strange при отсутствии явного задания файла журнала транзакций логическое имя этого файла будет Strange_log.

    Предложение FILENAME

    FILENAME = { '<путь к файлу>' | '<путь к файловому потоку>' }

    Предложение FILENAME определяет полный путь к файлу (включая имя внешнего носителя), а также имя самого создаваемого файла. Путь (все каталоги в пути) должен существовать на указанном внешнем носителе, а сам файл должен отсутствовать. Для первичного файла данных принято использовать расширение mdf, для вторичных файлов данных — расширение ndf, а для журнала транзакций — ldf. Такие значения расширений не являются обязательными, однако следование этому правилу опять же повышает читаемость скриптов и удобство в работе с системой.

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

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

    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

    ЗАМЕЧАНИЕ
    Пути по умолчанию для файла данных и файла журнала транзакций можно изменить в Management Studio. Для этого в Object Explorer нужно щелкнуть правой кнопкой мыши по имени сервера базы данных и в контекстном меню выбрать Properties. В появившемся окне свойств сервера нужно выбрать вкладку Database Settings и изменить пути в полях Data и Log для файлов данных и журналов транзакций соответственно.

    Именам файлов присваивается логическое имя файла с расширением mdf (файл данных) или ldf (журнал транзакций). Например, в приведенном только что примере с созданием базы данных Strange файл данных получит имя Strange.mdf, а файл журнала транзакций — Strange.ldf. Понятно, что по умолчанию всегда будет создаваться лишь один файл данных и только один файл журнала транзакций в одном и том же каталоге на внешнем носителе.

    Обратите внимание, что имя файла задается одной строковой константой. Здесь по не совсем понятной для меня причине недопустимо использование каких-либо выражений, внутренних переменных, операций. В частности, нельзя даже использовать простую операцию конкатенации строк. Первоначально складывается впечатление, что в пакете на создание базы данных динамически сформировать путь к файлу и имя файла вообще невозможно. Однако это не так. В Transact-SQL можно использовать очень полезный во многих случаях оператор EXECUTE, который позволяет динамически создавать в том числе и оператор CREATE DATABASE. Использование локальных переменных в таких ситуациях при создании базы данных см. далее в примерах. Кроме того, в утилите командной строки sqlcmd существует возможность вызывать на выполнение скрипт, содержащий параметры, значения которым подставляются при вызове этой утилиты. Такой пример мы также вскоре рассмотрим.

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

    Предложение SIZE

    [ SIZE = <целое1> [ KB | MB | GB | TB ] ]

    Необязательное предложение SIZE задает начальный размер файла. <Целое1> в предложении является целым числом, определяющим размер в указанных следом за ним единицах — в килобайтах (KB), мегабайтах (MB), гигабайтах (GB) или в терабайтах (TB). Если единица измерения не указана, то предполагаются мегабайты; обратите внимание, что в описании синтаксиса MB подчеркнуто, что, как мы помним, используется для указания значения по умолчанию. Ненавязчиво сообщу, что при создании реальных систем я всегда указываю единицу измерения.

    Размер любого файла не может быть меньше, чем 512 Кбайт, а первичный файл данных должен иметь размер не менее 3 Мбайт. <целое1> имеет целочисленный тип данных INTEGER, его значение не может превышать 2147483647. Здесь речь идет только о числовом значении самого параметра, а не о размере файла. Для задания больших размеров следует указывать соответствующие единицы измерения.
    Вы также не сможете задать начальный размер файла, который превышает объем свободного места на выбранном носителе вашего компьютера. При создании базы данных и при размещении ее файлов система выполнит и такую проверку.

    Если предложение SIZE не задано, то начальному размеру файла присваивается значение по умолчанию, определенное в системной базе данных model. В моей версии установленной системы файл данных по умолчанию получает начальный размер 3 Мбайта, а журнал транзакций — 1 Мбайт. Вряд ли эти значения изменятся при переходе к другим версиям системы.

    Предложение MAXSIZE

    [ MAXSIZE = { <целое2> [ KB | MB | GB | TB ] | UNLIMITED } ]

    Необязательное предложение MAXSIZE задает максимальный размер, который может получить файл при увеличении количества данных, помещаемых в файл, или указывает, что размер файла не ограничен (параметр UNLIMITED). В последнем случае файл будет увеличиваться в размерах на величину, указанную в ключевом слове FILEGROWTH, пока не будет исчерпано все свободное пространство носителя. На самом деле это не совсем так. "Неограниченность" размера означает лишь, что файл данных не может превышать 16 терабайт (для редакции Express используется другое ограничение — вся база данных по размеру не может превышать 10 Гбайт), а файл журнала транзакций — 2 Тбайта. Как и в случае задания начального размера файла, в этом предложении максимальный размер может задаваться в килобайтах, мегабайтах, гигабайтах и терабайтах (параметры KB, MB, GB и TB, соответственно).

    Значением единицы измерения по умолчанию также является мегабайт.
    Если это предложение не указано, то для файла данных задается неограниченный (unlimited) размер.

    Предложение FILEGROWTH

    [ FILEGROWTH = <целое3> [ KB | MB | GB | TB | % ] ]

    Необязательное предложение FILEGROWTH позволяет задать значение величины приращения размера файла. Параметр <целое3> задает увеличение размера в килобайтах, мегабайтах, гигабайтах, в терабайтах или в процентах от начального размера файла, как указано в этом предложении (KB, MB, GB, TB, %). Если единица измерения приращения не указана, то принимается мегабайт. Если это предложение вообще не указано, то для файла данных приращение задается в 1 Мбайт, а для журнала транзакций устанавливается приращение в 10% от начального размера файла.

    Как уже говорилось, размер страницы файла данных в базе имеет фиксированное значение 8 Кбайт (8192 байта) и не может быть изменен ни при создании базы данных, ни при ее изменении.

    Файловая группа

    Синтаксис описания файловой группы представлен в листинге 3.3 и в соответствующем R-графе (граф 3.7).

    Листинг 3.3. Синтаксис описания файловой группы

    <файловая группа> ::= FILEGROUP <имя файловой группы> [ CONTAINS FILESTREAM ] [DEFAULT] 
    <спецификация файла> [, <спецификация файла>]...


    Имя файловой группы должно быть уникальным среди имен файловых групп этой базы данных.
    Необязательное предложение CONTAINS FILESTREAM означает, что данная файловая группа предназначена только для хранения в файловой системе столбцов указанной таблицы с типом данных VARBINARY(MAX). Примеры использования файловых потоков мы рассмотрим в главе 5.

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

    ЗАМЕЧАНИЕ ПО СИНТАКСИСУ ОПЕРАТОРА CREATE DATABASE
    Кажется немного странным, что в языке Transact-SQL синтаксис оператора CREATE DATABASE для целей создания новой базы данных (другие варианты использования этого оператора мы рассмотрим ближе к концу главы) не позволяет явно установить начальные значения тому множеству характеристик базы данных, ее файловых групп и файлов, которые существуют в диалоговых средствах SQL Server. 

    Для изменения значений характеристик по умолчанию используется оператор ALTER DATABASE. Его мы очень скоро будем рассматривать довольно подробно. В диалоговых средствах Management Studio и в случае первоначального создания базы данных можно задавать значения для большинства характеристик. Полагаю, что такую возможность было бы полезно внести и в оператор CREATE DATABASE.

    Похожие новости

    Комментариев 0

    Информация
    Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.