Transact-SQL, Создание и удаление базы данных

Управление базой данных

Большинство авторов, начинают рассматривать SQL, начиная с операторов получения данных из базы данных. Именно с этого начинается рассмотрение SQL-92, но у нас еще нет баз данных и не откуда брать данные. Мы же начнем с самого начала, т.е. с создания базы данных, создания таблиц и изменения их структуры. Когда у нас будет готова тестовая база данных, мы заполним ее данными и тогда уже научимся работать с этими данными.

Итак, в этой главе нам предстоит узнать:

  • Как создавать базу данных с помощью SQL запросов;
  • Как изменять параметры базы данных;
  • Как создавать таблицы;
  • Как изменять параметры таблицы.

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

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

Операторы по описанию объектов базы данных выделают в отдельный язык (подязык SQL) - DDL (Data Definition Language, Язык Объявления Данных). Именно этот язык будет рассматриваться в этой главе, ведь нам предстоит научиться описывать данные таблицы.

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

Информация о каждой базе данных в SQL Server хранится в таблице sysdatabases базы данных master. Поэтому желательно (но не обязательно) использовать базу данных master, во время создания базы. К тому же, после изменения любой пользовательской базы данных создавать резервную копию базы данных master. О резервном копировании и восстановлении мы поговорим в разделе 4.10. Объявление базы данных – это процесс указания имени и указания размера и расположения файлов.

В Transact-SQL для создания базы данных есть команда CREATE DATABASE. Эта команда может выполняться только с сервером SQL Server. При использовании базы данных MS Access команда не доступна, потому что базой данных является файл с расширением .mdb, который создается в программе Access и к которому мы подключены.

Сервер MS SQL Server может содержать несколько баз данных. Вы можете подключиться к любой из них (системной или тестовой, которые присутствуют в стандартной поставке) и создать новую базу данных, но желательно подключаться к базе данных master.

Синтаксис команды создания базы данных показан в листинге 1.1.

Листинг 1.1. Создание базы данных

CREATE DATABASE имя
[ ON 
    [< filespec > [ ,...n ] ] 
    [ , < filegroup > [ ,...n ] ] 
] 
[ LOG ON { < filespec > [ ,...n ] } ] 
[ COLLATE имя_раскладки ]
[ FOR LOAD | FOR ATTACH ] 

< filespec > ::= 
[ PRIMARY ]
( [ NAME = логическое_имя_файла , ] 
    FILENAME = 'имя_файла_в_ОС' 
    [ , SIZE = размер ] 
    [ , MAXSIZE = { максимальный_размер | UNLIMITED } ] 
    [ , FILEGROWTH = увеличение ] ) [ ,...n ]

< filegroup > ::= 
FILEGROUP файловая_группа < filespec > [ ,...n ]

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

CREATE DATABASE имя

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

Если вы посмотрите на остальные параметры синтаксиса, то увидите, что все они находятся в квадратных скобках. Все, что в квадратных скобках – описывает не обязательные параметры. Получается, что минимальная команда создания базы выглядит как

CREATE DATABASE Имя

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

CREATE DATABASE [Тестовая база]

Очень интересной является следующая строка:

[ FOR LOAD | FOR ATTACH ]

Здесь в квадратных скобках указано два значения, разделенных вертикальной чертой. Это значит, что эти значения являются не обязательными, а вертикальная черта соответствует слову "или", т.е. в запросе можно будет указывать или FOR LOAD, или FOR ATTACH, или вообще ничего. Оба параметра указывать нельзя.

В угловых скобках указываются имена секций. Например, в описании оператора CREATE DATABASE есть два указания на < filespec >. Эта секция может идти после ключевого слова ON и после LOG ON. Описание самой секции идет после:

< filespec > ::=

Не понятно? Попробуем еще раз. Описание оператора CREATE DATABASE выглядит так:

CREATE DATABASE имя
[ ON 
    [< filespec > [ ,...n ] ] 
    [ , < filegroup > [ ,...n ] ] 
] 
[ LOG ON { < filespec > [ ,...n ] } ] 
[ COLLATE имя_раскладки ]
[ FOR LOAD | FOR ATTACH ] 

Далее идут описания секций < filespec > и < filegroup >:

< filespec > ::= 
[ PRIMARY ]
( [ NAME = логическое_имя_файла , ] 
    FILENAME = 'имя_файла_в_ОС' 
    [ , SIZE = размер ] 
    [ , MAXSIZE = { максимальный_размер | UNLIMITED } ] 
    [ , FILEGROWTH = увеличение ] ) [ ,...n ]

и

< filegroup > ::= 
FILEGROUP файловая_группа < filespec > [ ,...n ]

Теперь, заменяем в описании CREATE DATABASE название секции < filespec > на саму секцию. Если вы имеете опыт программирования на одном из высокоуровневых языков, то в секциях вы уже наверно увидели аналогию с процедурами. Название секции < filespec > аналогично имени процедуры, а после < filespec > ::= идет сам код процедуры.

Следующая интересная запись:

[ < filespec > [ ,...n ] ]

Запись < filespec > - описание файла, а [ ,...n ] указывает на то, что возможно несколько описаний.

С помощью круглых скобок параметры объединяются в группу, например:

( [ NAME = логическое_имя_файла , ] 
    FILENAME = 'имя_файла_в_ОС' 
    [ , SIZE = размер ] 
    [ , MAXSIZE = { максимальный_размер | UNLIMITED } ] 
    [ , FILEGROWTH = увеличение ] ) [ ,...n ]

В данном случае в группу объединены параметры NAME, FILENAME, SIZE, MAXSIZE и FILEGROWTH. Все эти параметры описывают файл, поэтому и объединены в группу. Из всей группы только параметр FILENAME является обязательным. После круглых скобок идет снова можно увидеть [ ,...n ], значит может быть несколько описаний файлов (для каждого файла базы данных свое описание).

Параметр FILENAME интересен еще и тем, что его значение задается с помощью знака равенства, после которого идет текст в одинарных кавычках:

FILENAME = 'имя_файла_в_ОС'

Кавычки в данном случае указывают на их обязательное присутствие в SQL запросе. По наличию кавычек достаточно просто определить тип параметра. Если они присутствуют, то параметр строковый, иначе числовой. Например, параметр SIZE не содержит кавычек, а значит, он числовой:

SIZE = 'Размер'

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

  • PRIMARY. Этот параметр указывает файл в основной файловой группе. Эта файловая группа содержит все системные базы данных. Она также содержит все объекты, не назначенные другим файловым группам. Каждая база данных содержит один основной файл данных. Основной файл – это стартовая точка базы данных и указывает на место ее нахождения. Рекомендуемое файловое расширение для основного файла .mdf. Если вы не укажите этого параметра, первый файл списка описания будет использован как основной.
  • FILENAME. Этот параметр указывает имя и путь к файлу в операционной системе. Путь должен указывать на папку на сервере, где установлен SQL Server. Нельзя использовать сетевые диски с других компьютеров.
  • SIZE. Этот параметр указывает размер файла данных или журнала. Вы можете указать размер в мегабайтах MB (значение по умолчанию) или в килобайтах KB. Минимальный размер – 512KB для обоих файлов – журнала транзакций и файла данных. Размер, указанный для основного файла базы данных должен быть больше или равен размеру основного файла базы данных model. Мы уже говорили, что база model копируется во все новые базы данных, поэтому размер новой, не может быть меньше размера model, иначе копирование станет невозможным. Когда вы добавляете новый файл базы данных или журнала без указания размера – то сервер использует значение размера по умолчанию = 1МБ.
  • MAXSIZE. Этот параметр указывает максимальный размер, до которого файл может увеличиваться. Вы можете указать размер в мегабайтах MB (значение по умолчанию) или в килобайтах KB. Если вы не укажите максимальный размер, файл будет увеличиваться, пока диск не будет заполнен полностью.
  • FILEGROW. Этот параметр указывает размер приращения файла. Значение этого параметра для файла не может превышать значение MAXSIZE. Значение 0 указывает на запрет увеличения. Значение может быть указано в мегабайтах (по умолчанию), килобайтах или процентах. Значение по умолчанию, если этот параметр не указан - 10%, а минимальный размер – 64кб. Указанный размер округляется до ближайшего числа, кратному 64кб.
  • COLLATION. Этот параметр указывает значение по умолчанию для сопоставления в базе данных. Сопоставления (кодировка или раскладка) включают роли контролирующие использование символов для языка и алфавита.

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

  1. Все базы данных имеют основной файл данных, определяемый именем файла с расширением .mdf, и один или более файлов журнала определяемый именем файла с расширением .ldf. База данных может также иметь вторичные файлы данных, которые определяются по имени файла с расширением .ndf. Файлы могут объединяться в группы, о чем мы поговорим в разделе 1.1.1.
  2. Физические файлы имеют двойное именование – имя ОС и имя, которые вы можете использовать в операторах Transact-SQL (логическое имя, которое указывается в параметре NAME).
  3. Когда вы создаете базу данных, в нее копируется содержимое базы данных model, которая включает системные таблицы и может содержать пользовательские таблицы, созданные вами. Минимальный размер создаваемой базы данных должен быть равен или больше размера базы данных model.
  4. Сервер SQL хранит, читает и записывает данные блоками по 8кб, эти блоки называются страницами. База данных может хранить 128 страниц на мегабайт (1 мегабайт или 1048576 байт разделить 8 килобайт или 8192 байт). Все страницы хранятся в пространстве. Пространство – это 8 последовательных страниц, или 64кб. Поэтому база данных имеет 16 пространств в мегабайте.

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

Сервер SQL использует два типа пространств:

  • Пространства, которые хранят страницы от двух и более объектов, называемые смешанными. Каждая таблицы начинается как смешанное пространство. Вы используете смешанное пространство главным образом для страниц, которые хранят пространство и содержат маленькие объекты.
  • Пространства, которые имеют все 8 страниц выделенных одному объекту, называемый однородным пространством. Они используются, когда таблице или индексу надо более 64 кб пространства.

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

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

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

Теперь рассмотрим, как можно удалять созданную базу данных, ведь нужно иметь возможность удалить тестовую базу данных, которую мы будем создавать для тестирования:

DROP DATABASE имя

Нельзя удалять базу данных если она:

  • Базу данных, которая открыта для чтения или записи любым пользователем, поэтому при удалении вы также не должны быть к ней подключены. Лучше всего подключиться к базе данных master;
  • Базу данных, которая опубликовала любую свою таблицу как часть репликации SQL Server
  • Системную базу данных

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

CREATE DATABASE имя

Все остальные параметры являются не обязательными. Попробуем создать базу данных с именем TestDatabase и удалить. Сначала создадим базу:

CREATE DATABASE TestDatabase

И тут же ее удалим:

DROP DATABASE TestDatabase

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

Теперь посмотрим, какие еще возможности дает нам команда создания базы данных. Но сначала вы должны знать, что имя базы данных может иметь не более 128 символов, если вы явно указываете логическое имя файла журнала. Я считаю, это вполне достаточно. Если логическое имя журнала не задано, то размер сокращается до 123 символов. Это связано с тем, что логическое имя журнала также имеет ограничение в 128 символов и если оно не указано, то в качестве имени используется имя базы плюс суффикс _log. Самое интересное, что суффикс занимает четыре символа, а 128-4=124. Почему Microsoft ограничивает имя до 123, для меня остается загадкой. Быть может, составители документации разучились считать?

Рассмотрим основные правила, которым должны подчиняться имена баз данных и объектов в ней:

  • Первый символ должен быть буква a-z, A-Z.
  • После первого символа может быть буква, цифра или символ _, @, или #.
  • Идентификаторы, начинающиеся с символов, имеют специальное назначение:
    • Идентификаторы, начинающиеся с символа @, являются локальными переменными или параметрами.
    • Идентификаторы, начинающиеся с символа #, являются временные таблицы или процедуры.
    • Идентификаторы, начинающиеся с символа ##, являются глобальными временными объектами.

Эти правила относятся ко всем именам – базы данных, таблиц, полей и так далее. С некоторыми из них мы еще будем не раз встречаться на практике, как например, создание временных объектов.

Если необходимо указать дополнительные параметры, то после оператора CREATE DATABASE нужно указать ON и далее в круглых скобках идут параметры:

CREATE DATABASE TestDatabase ON
(
 Параметры
)

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

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

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

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

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

Теперь давайте, рассмотрим, какие параметры можно указывать при создании файлов базы данных и журнала транзакций:

  • NAME – логическое имя, которое будет отображаться в SQL сервере;
  • FILE NAME – физическое расположение и имя файла;
  • SIZE – начальный размер файла. Это начальное значение, которое может увеличиваться по мере надобности.
  • MAXSIZE – максимальный размер файла. Чтобы файл не имел ограничений, необходимо указать в этом параметре UNLIMITED вместо реального значения. Но я рекомендую указывать такое значение, которое не сможет переполнить весь жесткий диск.
  • FILEGROWTH – на сколько должен увеличится размер файла, если текущего размера не достаточно. Это значение можно указывать как в реальных значениях (мегабайты) так и в процентном отношении к текущему размеру. Увеличение должно быть достаточным, чтобы эта операция выполнялась как можно реже.

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

Листинг 1.2. Создание базы данных с описанием параметров файлов

CREATE DATABASE TestDatabase
ON 
( 
  NAME = TestDatabase_data,
  FILENAME = 'c:\data\test.mdf',
  SIZE = 10MB,
  MAXSIZE = 100GB,
  FILEGROWTH = 5MB 
 )
LOG ON
( 
  NAME = 'TestDatabase_log',
  FILENAME = 'c:\data\test.ldf',
  SIZE = 5MB,
  MAXSIZE = 10GB,
  FILEGROWTH = 5% 
 )

Посмотрим, что делает вышеуказанный сценарий. В первой строке мы указываем, что необходимо создать базу данных с именем TestDatabase. Затем идет ключевое слово ON, и в круглых скобках указываются параметры файла базы данных. Мы указываем пять параметров: логическое имя (NAME), физическое расположения файла данных (FILENAME), начальный размер устанавливаем в 10 мегабайт (SIZE), максимальный размер ограничивается размером в 1000 гигабайт (MAXSIZE), а в качестве приращения указываем всего лишь 5 мегабайт. Этого достаточно только для тех баз данных, где добавление новых записей происходит не часто.

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

Если база данных не изменяется в размере (не добавляются новые данные), но очень часто происходит изменение уже существующих данных, то приращение для файла данных можно сделать небольшим (5-10 мегабайт, зависит от размера одной записи таблицы). При этом файл журнала должен иметь большее приращение и лучше всего в процентах от существующего размера. Исходя из практики, я бы порекомендовал значение 10%, но в зависимости от задачи, значение можно скорректировать.

Если данные базы используются только как справочник и изменяются очень редко, то размер приращения базы и журнала можно сделать минимальным. Запросы на выборку данных не сохраняются в журнале транзакций, поэтому можно ограничиться увеличением в 1 мегабайт. Но при этом, значение приращения желательно сделать больше размера 100 строк данных. Размер строки можно примерно рассчитать по размеру всех полей самой большой таблицы.

Если вы используете автоматическое увеличение, то лучше всего указать максимальный размер. Это позволит вам предотвратить заполнение базой данных всего жесткого диска.

Приращение для файла данных можно указывать и в процентах, например:

CREATE DATABASE TestDatabase
ON 
( 
  NAME = TestDatabase_data,
  FILENAME = 'c:\data\test.mdf',
  SIZE = 10MB,
  MAXSIZE = 100GB,
  FILEGROWTH = 10% 
 )

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

С помощью команды создания базы данных, можно подключать уже существующие файлы. Не путайте с восстановлением данных, когда база восстанавливается из резервной копии. Подключение – это создание базы данных из существующего mdf файла, а не резервной копии. Файл журнала в этом случае желателен, но не обязателен. Более подробно о подключении и отключении базы можно узнать в разделах 1.1.2 и 4.12.

Когда создается новая база данных, она дублирует базу данных model. Любые опции и настройки в базе данных model копируются в новую базу данных. Если вам необходимо создать несколько схожих баз или все баши базы данных должны обладать схожими свойствами, то можно внести необходимые изменения в model.

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

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

Напоследок отметим одно замечание. При создании базы данных, нельзя указывать только параметры журнала, без описания файла данных. Это значит, что следующий запрос завершиться ошибкой:

CREATE DATABASE TestDatabase2
LOG ON
(
   NAME = 'TestDatabase_log',
   FILENAME = 'c:\data\log',
   SIZE = 5MB,
   MAXSIZE = 10GB,
   FILEGROWTH = 5%
)

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

CREATE DATABASE TestDatabase2
ON
(
   NAME = 'TestDatabase',
   FILENAME = 'c:\data\data',
)
LOG ON
(
   NAME = 'TestDatabase_log',
   FILENAME = 'c:\data\log',
   SIZE = 5MB,
   MAXSIZE = 10GB,
   FILEGROWTH = 5%
)

Файловые группы

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

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

Распределение файлов на несколько физических дисков позволяет повысить производительность базы данных. У каждого диска свой контроллер и каждый из них может параллельно производить операции чтения/записи.

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

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

Сервер SQL поддерживает следующие два типа файловых групп:

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

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

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

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

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

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

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

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

Итак, давайте создадим базу данных, в которой будет две файловые группы: обязательная PRIMARY (в нее поместим два файла) и пользовательская FILEGR1 (в ней разместим 3 файла). Код создания можно увидеть в листинге 1.3.

Листинг 1.3. Создание базы данных с файловыми группами

CREATE DATABASE TestBatabase
ON PRIMARY
( 
  NAME = tdb1_dat,
  FILENAME = 'c:\data\tdb1_data.mdf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 10MB 
 ),
( 
  NAME = tdb2_dat,
  FILENAME = 'd:\data\tb2_data.ndf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 10MB 
 ),
FILEGROUP DBGroup1
( 
  NAME = tdb_group1_dat,
  FILENAME = 'e:\data\tdb_gr1_data.ndf',
  SIZE = 10MB,
  FILEGROWTH = 5% 
 ),
( 
  NAME = tdb_group2_dat,
  FILENAME = 'f:\data\tdb_gr2_data.ndf',
  SIZE = 10MB,
  FILEGROWTH = 5% 
 ),
( 
  NAME = tdb_group3_dat,
  FILENAME = 'g:\data\tdb_gr3_data.ndf',
  SIZE = 10MB,
  FILEGROWTH = 5% 
)

Перед описанием первого файла, мы явно указываем, что он будет создан в основной файловой группе (ON PRIMARY). После этого в круглых скобках через запятую идет описание двух файлов. Потом создается новая файловая группа DBGroup1 с помощью оператора FILEGROUP:

FILEGROUP DBGroup1

И теперь идет описание файлов для этой файловой группы. Обратите внимание, что каждый файл находиться на своем жестком диске. Это позволит добиться параллельного чтения записи в каждый файл. На рисунке 1.1 вы можете видеть окно свойств созданной базы данных с несколькими файлами (окно программы Enterprise Manager). В последней колонке списка показана файловая группа файла данных. Единственное, на рисунке все файлы находятся на одном диске, потому что у меня в компьютере нет необходимого количества разделов, потому что у меня не сервер, а простой ноутбук. Но на рабочих серверах лучше устанавливать по несколько дисков и распределять между ними нагрузку.

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

Подключение базы данных

Когда подключение удобнее и даже выгоднее резервирования? Недавно я столкнулся с такой проблемой, что настройки репликации не попадают в резервную копию. Это оказалось серьезной проблемой. У меня на работе есть два офиса, который находятся в разных концах города и не соединены через Интернет. Чтобы производить репликацию, базу данных удаленного офиса нужно перевозить в главный офис, но через резервную копию этого не получилось. База данных восстанавливается, как будто никаких настроек репликации не было.

Первое, что приходит в голову после такого разочарования – таскать жесткий диск или весь системный блок для репликации в основной офис. Это, конечно же, не очень удачный выход. И вот однажды мы попытались отключить базу данных на удаленном сервере, привести в центральный офис и подключить. Настройки репликации сохранились!

Для отключения базы данных используется системная процедура sp_detach_db. С процедурами мы пока еще не знакомы и это тема 3-й главы, поэтому пока просто выполните следующую команду для отключения:

EXEC sp_detach_db 'TestDatabase', 'true'

Про отключение баз данных мы поговорим в разделе 4.12. Там же процедура sp_detach_db будет рассмотрена более подробно.

Посмотрим, как можно подключить файл базы данных к серверу с помощью оператора CREATE DATABASE:

CREATE DATABASE DatabaseName
ON PRIMARY (FILENAME = 'c:\data\filename.mdf')
FOR ATTACH

В первой строке указаны ключевые слова CREATE DATABASE, после которых указывается имя подключаемой базы данных. Ключевое слово ON PRIMARY означает создание в основной файловой группе. После этого в круглых скобках указывается путь к существующему файлу данных. И в последней строке указываем FOR ATTACH, то есть для подключения.

Обратите внимание, что имя подключаемой базы отличается от имени базы, которую мы отключали. Раньше имя было TestDatabase, а после подключения оно превратилось в Archive. Таким образом, мы смогли переименовать уже существующую базу данных. С помощью оператора ALTER DATABASE, который используется для редактирования параметров (см. разд. 1.3) базы переименование невозможно.

Сопоставление

У нас остался еще один параметр, который мы не рассмотрели – это Collation (сопоставление). Что это такое? У начинающих администраторов он вызывает страх, а у опытных – уважение. С помощью Collation можно указать раскладку (кодировку), которая будет использоваться по умолчанию для заданной базы данных. В MS SQL Server существует три способа задать раскладку (кодировку):

  1. Для каждого поля в отдельности. Вы можете указать кодировку конкретного поля таблицы. Если у поля не указана кодировка, то будет взято значение, указанное для базы данных;
  2. Для базы данных в целом. Указанная кодировка для базы данных будет использоваться по умолчанию для всех полей таблиц, если не указано иного. Если при создании базы данных параметр Collation не задан, то будет использоваться значение по умолчанию, указанное для сервера баз данных в целом.
  3. Глобальная кодировка. Это значение задается во время установки MS SQL Server и по умолчанию устанавливается в соответствии с региональными настройками ОС сервера.

Самый простой вызов команды:

CREATE DATABASE TestDatabase1
COLLATE Cyrillic_General_CI_AS

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

Листинг 1.4. Создание базы данных с описанием параметров файлов и кодировки

CREATE DATABASE TestDatabase2
ON 
( 
  NAME = TestDatabase_data,
  FILENAME = 'c:\data\test.mdf',
  SIZE = 10MB,
  MAXSIZE = 100GB,
  FILEGROWTH = 5MB 
)
LOG ON
( 
  NAME = 'TestDatabase_log',
  FILENAME = 'c:\data\test.ldf',
  SIZE = 5MB,
  MAXSIZE = 10GB,
  FILEGROWTH = 5% 
)
COLLATE Cyrillic_General_CI_AS

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

SELECT *
FROM ::fn_helpcollations()

На данном этапе не будем останавливаться на том, что здесь происходит, потому что запросы SELECT это тема второй главы. Замечу только, что в результате на экране вы увидите таблицу из двух колонок name (имя) и description (описание).

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

Предыдущая глава

Transact-SQL Введение

Следующая глава

Создание таблиц в Transact-SQL

О блоге

Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю

Обратная связь

Без проблем вступаю в неразборчивые разговоры по e-mail. Стараюсь отвечать на письма всех читателей вне зависимости от страны проживания, вероисповедания, на русском или английском языке.

Пишите мне