Transact-sql, створення бази даних

В організації бази даних задіюється велика кількість різних об'єктів. Всі об'єкти бази даних є або фізичними, або логічними. Фізичні об'єкти пов'язані з організацією даних на фізичних пристроях (дисках). Фізичними об'єктами компонента Database Engine є файли та файлові групи. Логічні об'єкти є одними уявленнями бази даних. Як приклад логічних об'єктів можна назвати таблиці, стовпці та подання (віртуальні таблиці).

Для створення бази даних використовується два основні методи. У першому методі задіюється оглядач об'єктів середовища SQL Server Management Studio, як було показано раніше, а в другому застосовується інструкція мови Transact-SQL CREATE DATABASE. Далі наводиться загальна форма цієї інструкції, а потім детально розглядаються її складові:

Параметр db_name - це ім'я бази даних. Ім'я бази даних може містити максимум 128 символів. Одна система може управляти до 32 767 базами даних. Всі бази даних зберігаються в файлах, які можуть бути вказані явно адміністратором або надані неявно системою. Якщо інструкція CREATE DATABASE містить параметр ON. всі файли бази даних вказуються явно.

Параметр file_spec1 представляє специфікацію файлу і сам може містити додаткові опції, такі як логічне ім'я файлу, фізичне ім'я і розмір. Параметр PRIMARY вказує перший (і найбільш важливий) файл, який містить системні таблиці та іншу важливу внутрішню інформацію про базу даних. Якщо параметр PRIMARY відсутня, то в якості первинного файлу використовується перший файл, вказаний в специфікації.

Опція LOG ON параметра dbo визначає один або більше файлів в якості фізичного сховища журналу транзакцій бази даних. Якщо опція LOG ON відсутня, то журнал транзакцій бази даних все одно буде створений, оскільки кожна база даних повинна мати, принаймні, один журнал транзакцій. (Компонент Database Engine веде облік всіх змін, які він виконує з базою даних. Система зберігає всі ці записи, особливо значення до і після транзакції, в одному або більше файлів, які називаються журналами транзакцій. Для кожної бази даних системи ведеться її власний журнал транзакцій.)

В опції COLLATE вказується порядок сортування за замовчуванням для бази даних. Якщо опція COLLATE не вказана, базі даних присвоюється порядок сортування за замовчуванням, абсолютно такий же, як і порядок сортування за замовчуванням системи баз даних.

В опції FOR ATTACH вказується, що база даних створюється за рахунок підключення існуючого набору файлів. При використанні цієї опції потрібно явно вказати перший первинний файл. В опції FOR ATTACH_REBUILD_LOG вказується, що база даних створюється шляхом приєднання існуючого набору файлів операційної системи.

Компонент Database Engine створює нову базу даних за шаблоном зразковою бази даних model. Властивості бази даних model можна налаштовувати для задоволення персональних концепцій системного адміністратора. Якщо певний об'єкт бази даних повинен бути присутнім в кожній користувальницької базі даних, то цей об'єкт слід спочатку створити в базі даних model.

Код, наведений у прикладі, створює базу даних, яка називається SampleDb. Така скорочена форма інструкції CREATE DATABASE можлива завдяки тому, що майже всі її параметри мають значення за замовчуванням. За замовчуванням система створює два файли. Файл даних має логічне ім'я SampleDb і вихідний розмір 2 Мбайт. А файл журналу транзакцій має логічне ім'я SampleDb_log і вихідний розмір 1 Мбайт. (Значення розмірів обох файлів, а також інші властивості нової бази даних залежать від відповідних специфікацій бази даних model.)

У прикладі нижче показано створення бази даних з явною вказівкою файлів бази даних і журналу транзакцій:

Створена в прикладі база даних називається Projects. Оскільки опція PRIMARY не вказана, то первинним файлом передбачається перший файл. Цей файл має логічне ім'я projects_dat і він зберігається в дисковому файлі projects.mdf. Початковий розмір цього файлу 10 Мбайт. При необхідності, система виділяє цього файлу додатковий дисковий простір в збільшеннях по 5 Мбайт. Якщо не вказати опцію MAXSIZE або якщо цієї опції присвоєно значення UNLIMITED, то максимальний розмір файлу може збільшуватися і буде обмежуватися тільки розміром всього дискового простору. (Одиницю розміру файлу можна вказувати за допомогою суфіксів KB, TB і MB, що означають кілобайти, терабайти і мегабайти відповідно. За замовчуванням використовується одиниця розміру MB, тобто мегабайти.)

Крім файлу даних створюється файл журналу транзакцій, який має логічне ім'я projects_log і фізичне ім'я projects.ldf. Всі опції специфікації файлу журналу транзакцій мають такі ж імена і значення, як і відповідні опції для специфікації файлу даних.

У мові Transact-SQL можна вказати конкретний контекст бази даних (тобто яку базу даних використовувати в якості поточної) за допомогою інструкції USE. (Альтернативний спосіб - вибрати ім'я необхідної бази даних в списку Database (Бази даних) в панелі інструментів середовища SQL Server Management Studio.)

Системний адміністратор може призначити користувачеві поточну базу даних за замовчуванням за допомогою інструкції CREATE LOGIN або інструкції ALTER LOGIN. В такому випадку користувачам не потрібно виконувати інструкцію USE, якщо тільки вони не хочуть використовувати іншу базу даних.

Створення моментального знімка бази даних

Крім створення нової бази даних, інструкцію CREATE DATABASE можна застосувати для отримання моментального знімка існуючої бази даних (база даних-джерело). Моментальний знімок бази даних є узгодженою з точки зору завершених транзакцій копією вихідної бази даних на момент створення знімка. Далі показаний синтаксис інструкції для створення знімка бази даних:

Таким чином, щоб створити моментальний знімок бази даних, в інструкцію CREATE DATABASE потрібно вставити пропозиція AS SNAPSHOT OF. У прикладі нижче ілюструється створення моментального знімка бази даних SampleDb і збереження його в папці D: \ temp. (Перш ніж виконувати цей приклад, потрібно створити даний каталог.)

Моментальний знімок існуючої бази даних - це доступна тільки для читання копія бази даних-джерела, яка відображає стан цієї бази даних на момент копіювання. (Таким чином, можна створювати множинні моментальні знімки існуючої бази даних.) Файл моментального знімка (в прикладі вище це файл D: \ temp \ snapshot_DB.mdf) містить тільки змінені дані бази даних-джерела. Тому в коді для створення знімка необхідно вказувати логічне ім'я кожного файлу даних бази даних-джерела, а також відповідні фізичні імена.

Оскільки моментальний знімок містить тільки змінені дані, то для кожного знімка потрібно лише невелика частка дискового простору, необхідного для відповідної бази даних-джерела.

Знімки баз даних можна створювати тільки на дисках з файловою системою NTFS (New Technology File System - файлова система нової технології), тому що тільки ця файлова система підтримує технологію розріджених файлів, яка застосовується для зберігання моментальних знімків.

Знімки баз даних зазвичай застосовуються в якості механізму запобігання даних від спотворення.

Приєднання і від'єднання баз даних

Всі дані бази даних можна від'єднати, а потім знову приєднати до цього ж або іншого сервера бази даних. Ця функціональність використовується при переміщенні бази даних.

Для від'єднання бази даних від сервера баз використовується системна процедура sp_detach_db. (Від'єднується база даних повинна знаходитися в режимі одного.)

Для приєднання бази даних використовується інструкція CREATE DATABASE з пропозицією FOR ATTACH. Для приєднується бази даних повинні бути доступними всі необхідні файли. Якщо який-небудь файл даних має шлях, що відрізняється від початкового шляху, то для цього файлу необхідно вказати поточний шлях.