3.4.1.3. Создание и отображение баз данных в командной строке


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

Универсальной утилитой командной строки, позволяющей выполнять операторы Transact-SQL в SQL Server, является sqlcmd. Описание наиболее часто используемых параметров этой утилиты см. в приложении 3.

Для создания базы данных запустите на выполнение командную строку или Windows PowerShell. На экране появится соответствующее окно и подсказка, например:

PS C:\Users\Administrator>

подсказке этого окна введите имя утилиты — sqlcmd. Если на вашем компьютере установлен единственный экземпляр сервера базы данных или вам требуется экземпляр сервера по умолчанию, то при вызове утилиты можно не задавать больше никаких параметров. На моем компьютере установлено два экземпляра SQL Server. 
Для вызова утилиты, которая должна будет работать с версией Express Edition, требуется задать параметр -S, в котором нужно указать имя сервера и через обратную наклонную черту имя экземпляра сервера:

sqlcmd -S DEVRACE\SQLEXPRESS

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

sqlcmd -S DEVRACE

Сам экземпляр сервера, компонент Database Engine, должен в момент вызова утилиты уже выполняться.
На экране появится подсказка самой утилиты:

1>

Давайте вначале отобразим существующие в экземпляре сервера базы данных, используя системное представление sys.databases. Введите в строке подсказки утилиты вначале команду USE, указывающую, что текущей базой данных является системная база данных master. После этой команды следует ввести GO, а затем оператор SELECT, обращающийся к системному представлению sys.databases. Нажмите клавишу <Enter> (пример 3.1).

Пример 3.1. Отображение баз данных текущего экземпляра сервера базы данных в системном представлении sys.databases

USE master;
GO
SELECT name, database_id, create_date, collation_name
FROM sys.databases;

Однако ничего интересного не произойдет, только на экране появятся введенные строки и следующая строка подсказки утилиты с номером 4:

1> USE master;
2> SELECT name, database_id, create_date, collation_name
3> FROM sys.databases;
4>

Для того чтобы был выполнен введенный оператор Transact-SQL (или группа операторов), нужно ввести еще и оператор GO. После ввода этого оператора и нажатия клавиши <Enter> появится список всех баз данных, существующих в экземпляре сервера. Если вы еще не создавали пользовательские базы данных, то список будет содержать только описания четырех системных баз данных: master, tempdb, model и msdb и двух баз данных, относящихся к компоненту Reporting Services и используемых для внутренних целей: ReportServer и ReportServerTempDB. Скрытая база данных resource не отображается в этом списке.
Оператор SELECT, используемый в этом примере, позволяет получить указанные данные из таблицы (таблиц) базы данных или из представления. Он также позволяет просто вывести заданные величины: любые литералы, константы, результаты обращения к различным функциям. В этом случае в операторе не задается предложение FROM.

В примере 3.1 после ключевого слова SELECT мы перечислили в списке выбора этого оператора имена тех характеристик (столбцов) представления, которые хотим отобразить, а в предложении FROM указали имя представления, из которого должны быть получены эти характеристики: sys.databases. В результате выполнения такого оператора мы получим список всех баз данных, существующих в текущем экземпляре сервера базы данных.

ЗАМЕЧАНИЕ
Следует напомнить, что ключевые слова языка Transact-SQL нечувствительны к регистру — их можно вводить как строчными, так и прописными буквами. Однако полученный результат не производит хорошего впечатления. Сведения по каждой базе данных занимают несколько строк. Это сильно ухудшает читаемость результата. И дело не только в том, что по умолчанию длина строки в PowerShell составляет 120 символов, а в командной строке 80. Размер строки можно изменить, щелкнув правой кнопкой мыши по заголовку окна, выбрав элемент меню Свойства и изменив на вкладке Расположение размер окна по ширине. Неприятность в том, что размер отображаемых полей слишком велик.

Для улучшения читаемости результата внесем некоторые изменения в наш оператор SELECT, выполнив простые преобразования получаемых данных и задав осмысленные тексты заголовков отображаемых столбцов. В подсказке утилиты введите и выполните несколько измененный оператор выборки данных (пример 3.2):

Пример 3.2. Более правильное отображение в PowerShell баз данных текущего экземпляра сервера базы данных в системном представлении sys.databases

USE master;
GO
SELECT CAST(name AS CHAR(20)) AS 'NAME',
CAST(database_id AS CHAR(4)) AS 'ID',
create_date AS 'DATE',
CAST(collation_name AS CHAR(23)) AS 'COLLATION'
FROM sys.databases;
GO

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

NAME
ID 
DATE
COLLATION
--------------------------
----------------------------------------------------
--------------------------
master
12003-04-08 09:13:36.390
Cyrillic_General_CI_AS
tempdb
22012-06-08 16:39:42.653
Cyrillic_General_CI_AS
model
32003-04-08 09:13:36.390
Cyrillic_General_CI_AS
msdb
42012-02-10 21:02:17.770
Cyrillic_General_CI_AS
ReportServer
52012-06-07 23:37:51.727
Latin1_General_CI_AS_KS
ReportServerTempDB
62012-06-07 23:37:53.120
Latin1_General_CI_AS_KS
(6 row(s) affected)

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

CAST(<идентификатор> AS <тип данных>)

Эта функция позволяет привести тип данных заданной переменной или константы (параметр идентификатор) к типу данных, указанному после ключевого слова AS (параметр тип данных). Здесь мы используем эту функцию лишь с целью уменьшения размера поля, отводимого для отображения столбца. Например, имя базы данных может содержать до 128 символов. Если фактическое имя короче, то справа при его отображении система добавляет недостающие пробелы до максимального значения 128. Мы же в операторе при помощи функции CAST() сократили этот размер до 20 символов, преобразовав исходный строковый тип данных у столбца name (CHAR(128)) с количеством символов 128 опять же в строковый, но с другим размером, указав в выходном типе данных 20 символов (CHAR(20)).
Мы в операторе SELECT только не задали никакого преобразования для столбца, содержащего дату и время, поскольку преобразование, выполняемое по умолчанию при отображении этого типа данных, нас вполне устраивает.

После имени отображаемого столбца в операторе SELECT мы можем указать предложение AS (не путайте с параметром AS в функции преобразования данных CAST()) и задать в этом предложении в апострофах текст, который будет отображаться в заголовке соответствующего столбца. Именно это мы и сделали для каждого выбираемого столбца. Разумеется, здесь мы также можем задать и русскоязычные заголовки — 'Имя базы данных', 'Идентификатор', 'Дата и время создания', 'Порядок сортировки', хотя размер таких заголовков будет иметь несколько большую длину. Результирующий размер отображаемых столбцов будет соответствовать большему значению из заданного размера отображаемых данных и размера заголовка.

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

Несколько слов об операторе GO. Он не является оператором языка Transact-SQL, по этой причине он не может завершаться символом точка с запятой, наличие этого символа вызовет синтаксическую ошибку. Это служебный оператор утилиты sqlcmd и программы Management Studio. В SQL Server существует понятие пакета операторов (batch). Пакет содержит группу операторов Transact-SQL; фактическое выполнение группы начинается только после ввода оператора GO. Синтаксис оператора GO:

GO [<количество повторений>]

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

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

Теперь немного усложним оператор SELECT, указав, что в список вывода должны помещаться только сведения по базе данных tempdb. Рассмотрим один из простых вариантов предложения WHERE в операторе SELECT. Введите и выполните следующие операторы (пример 3.3).

Пример 3.3. Отображение одной базы данных в системном представлении sys.databases

USE master;
GO
SELECT CAST(name AS CHAR(20)) AS 'NAME',
CAST(database_id AS CHAR(4)) AS 'ID',
create_date AS 'DATE',
CAST(collation_name AS CHAR(23)) AS 'COLLATION'
FROM sys.databases
WHERE name = 'tempdb';
GO

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

NAME 
ID
DATE
COLLATION
-----------------------
-----------------------
-----------------------
-----------------------
tempdb
22012-06-08 17:16:42.653
Cyrillic_General_CI_AS
(1 row(s) affected)

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

* * *

Здесь я хочу сделать небольшое отступление и сказать несколько слов об используемых в работе программных средствах и вообще об удобстве в работе. Утилиту sqlcmd можно запускать на выполнение в PowerShell и в обычной командной строке.

Результаты будут весьма похожими. Позже мы сможем сравнить вид этих отображений с тем, что можно получить в программе Management Studio. Надо сказать, что программисты имеют самые различные эстетические пристрастия. Кто-то предпочитает графический интерфейс (таких, разумеется, большинство), но есть люди, искренне любящие командную строку в различных ее вариантах и в принципе не признающие графический интерфейс. Правда, среди наших пользователей (как "юзеров", так и "ламеров") таких я что-то не встречал.

* * *

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

Пример 3.4. Отображение баз данных и их файлов в системном представлении sys.master_files

USE master;
GO
SELECT database_id, file_id, type, type_desc, data_space_id, name, physical_name, is_read_only, state, state_desc, size, max_size, growth, is_percent_growth
FROM sys.master_files;
GO

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

Пример 3.5. Более правильный вариант отображения баз данных и их файлов в системном представлении sys.master_files

USE master;
GO
SELECT CAST(database_id AS CHAR(5)) AS 'DB ID',
CAST(type_desc AS CHAR(6)) AS 'Descr',
CAST(name AS CHAR(24)) AS 'File Name',
CAST(state_desc AS CHAR(5)) AS 'State',
CAST(size AS CHAR(5)) AS 'Size',
max_size AS 'Max Size',
CAST(growth AS CHAR(6)) AS 'Growth'
FROM sys.master_files;
GO

Результатом будет отображение всех файлов баз данных текущего экземпляра сервера с некоторыми их характеристиками:

DB ID
Descr File
Name
State
Size
Max Size
Growth
-----------
-----------
-----------
-----------
-----------
-----------
-----------
ROWS
master
ONLIN
624  
-1
10
1
LOG
mastlog
ONLIN
224  
-1
10
2ROWS
tempdev
ONLIN
1024
-1
10
2LOG
templog
ONLIN
64
-1
10
3ROWS
modeldev
ONLIN
520
-1
128
3LOG
modellog
ONLIN
128
-1
10
4ROWS
MSDBData
ONLIN
2136
-1
10
4LOG
MSDBLog
ONLIN
584
268435456
10
5ROWS
ReportServer
ONLIN
648
-1
128
5LOG
ReportServer_log
ONLIN
880
268435456
10
6ROWS
ReportServerTempDB
ONLIN
520
-1
128
6LOG
ReportServerTempDB_log
ONLIN
130
268435456
10
(12 row(s) affected)

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

Изменим обращение к системному представлению sys.master_files следующим образом, используя системную функцию DB_NAME(), позволяющую по идентификатору, получаемому из этого представления, находить имена баз данных. Простоты ради уберем некоторые столбцы. Выполните пример 3.6.

Пример 3.6. Отображение баз данных и их файлов в системном представлении sys.master_files

USE master;
GO
SELECT CAST(DB_NAME(database_id) AS CHAR(20)) AS 'DB Name',
CAST(NAME AS CHAR(24)) AS 'File Name',
CAST(state_desc AS CHAR(7)) AS 'State',
CAST(type_desc AS CHAR(6)) AS 'Descr'
FROM sys.master_files
ORDER BY 'DB Name';
GO

Результатом будет следующий список:

DB Name
File Name
State
Descr
-----------
-----------
-----------
-----------
master
master
ONLINE
ROWS
master
mastlog
ONLINE
LOG
model
modeldev
ONLINE
ROWS
model
modellog
ONLINE
LOG
msdb
MSDBData
ONLINE
ROWS
msdb
MSDBLog
ONLINE
LOG
ReportServer
ReportServer
ONLINE
ROWS
ReportServer
ReportServer_log
ONLINE
LOG
ReportServerTempDB
ReportServerTempDB
ONLINE
ROWS
ReportServerTempDB 
ReportServerTempDB_log
ONLINE
LOG
tempdb 
tempdev
ONLINE
ROWS
tempdb 
templog
ONLINE
LOG
(12 row(s) affected)

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

В этом пакете в операторе SELECT мы добавили еще одну возможность. Последней строкой оператора записано ORDER BY 'DB Name'. Это предложение позволяет упорядочить отображаемый список по значению первого поля, указанного в списке выбора оператора, что мы и видим в результате отображения. Причем мы указали не имя столбца, получаемого из системного представления, не его номер (эти варианты тоже возможны в операторе SELECT), а текст заголовка, заданный нами после ключевого слова AS в списке выбора.

Предложение ORDER BY можно задать и в виде ORDER BY 1. Здесь указывается, что упорядочение осуществляется по первому полю из списка выбора. Результат выполнения будет, разумеется, точно таким же.
Другое системное представление просмотра каталогов sys.database_files позволяет просмотреть все файлы одной текущей базы данных, заданной в операторе USE.
Введите и выполните следующие операторы для отображения файлов базы данных, скажем, master (пример 3.7).

Пример 3.7. Отображение файлов базы данных master в системном представлении sys.database_files

USE master;
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (12)) AS 'Name',
state AS 'State',
CAST(state_desc AS CHAR(10)) AS 'State desc',
CAST(size AS CHAR(5)) AS 'Size'
FROM sys.database_files;
GO

Результат будет следующим:

ID 
Type
Description
Name
State
State desc
Size
------------
------------
------------
------------
------------
------------
------------
10
ROWS
master  
0
ONLINE
624
21LOG
mastlog  
0
ONLINE
224
(2 row(s) affected)

В точности такой же результат мы можем получить, используя и системное представление sys.master_files при задании в операторе SELECT предложения WHERE, в котором будет указан требуемый идентификатор нужной нам базы данных. Для системной базы данных master, как мы можем видеть из листинга примера 3.2, этот идентификатор равен единице. Выполните операторы примера 3.8:

Пример 3.8. Отображение файлов базы данных master в системном представлении sys.master_files

USE master;
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (12)) AS 'Name',
state AS 'State',
CAST(state_desc AS CHAR(10)) AS 'State desc',
CAST(size AS CHAR(5)) AS 'Size'
FROM sys.master_files
WHERE database_id = 1;
GO

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

На самом деле здесь можно и в одном операторе осуществить поиск идентификатора нужной базы данных, используя оператор SELECT, который обращается к системному представлению sys.databases. Для этого в предыдущем примере предложение WHERE нужно записать в следующем виде:

WHERE database_id =
( SELECT database_id
FROM sys.databases
WHERE name = 'master' );

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

Есть еще более простой способ выполнить нужное нам отображение файлов базы данных master, используя системную функцию DB_ID(), которая возвращает идентификатор базы данных по ее имени. Эту функцию мы будем еще не один раз использовать в наших скриптах. Синтаксис функции:

DB_ID([<имя базы данных>])

Если указанная в параметре база данных отсутствует в системе, то функция вернет значение NULL.
Введите и выполните операторы примера 3.9.

Пример 3.9. Лучший вариант отображения файлов базы данных master в системном представлении sys.master_files

USE master;
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (12)) AS 'Name',
state AS 'State',
CAST(state_desc AS CHAR(10)) AS 'State desc',
CAST(size AS CHAR(5)) AS 'Size'
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

Если в функции DB_ID() не указать необязательный параметр имя базы данных, то она вернет идентификатор текущей базы данных, которая была задана в последнем операторе USE.

ЗАМЕЧАНИЕ
Если вам нужно отобразить только сведения по одному из файлов базы данных (это показано в примерах Books Online), то в предложении WHERE оператора SELECT можно задать имя столбца name и после знака равенства в апострофах имя интересующего вас файла, например name = 'master'. В этом случае вы получите сведения только по файлу данных базы данных master. Однако если в системе (в текущем экземпляре сервера базы данных) у различных баз данных существуют файлы с тем же именем, то вы получите список всех таких файлов. Так что наш с вами вариант отображения файлов конкретной базы данных из примера 3.9 много лучше всех других.

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

FILE_NAME(<идентификатор файла>)

Идентификатором может быть любое число. Если в текущей базе данных существует файл с таким идентификатором, то функция вернет его логическое имя. Иначе функция возвращает значение NULL. В качестве идентификатора вы можете указать и дробное число. В этом случае дробная часть просто отбрасывается (округление не выполняется). Можно задать нулевое значение (напомню — файлы в базе данных нумеруются, начиная с единицы) и даже отрицательное значение; результат, возвращенный функцией при таких значениях параметра, будет NULL, ошибка сгенерирована не будет.
Выполните следующие операторы (пример 3.10).

Пример 3.10. Отображение логических имен файлов базы данных master в системной функции FILE_NAME()

USE master;
GO
SELECT DB_ID() AS 'ID',
CAST(FILE_NAME(1) AS CHAR(10)) AS 'Файл 1',
CAST(FILE_NAME(2) AS CHAR(10)) AS 'Файл 2',
CAST(FILE_NAME(3) AS CHAR(10)) AS 'Ничего';
GO

Результатом будет:
ID
Файл 1
Файл 2
Ничего
----------
----------
----------
----------
1master
mastlog
NULL
(1 row(s) affected)

Здесь в операторе USE указывается база данных master, к которой будут обращаться по умолчанию все следующие операторы. В операторе SELECT для имен логических файлов мы также выполняем преобразование данных, чтобы результат поместился в одну строку. По ходу дела в этом операторе мы отображаем и идентификатор текущей базы данных. В функции DB_ID() мы не указали никакого имени базы данных, поэтому функция вернет идентификатор текущей базы данных, т. е. master. 
В четвертом столбце задается несуществующий у базы данных номер файла — 3.

Результатом, как мы видим, будет значение NULL.
Обратите внимание, что в данном примере в операторе SELECT не указывается предложение FROM, т. е. не говорится, откуда должны получаться результаты — из какой таблицы, из какого представления. Это означает, что на выходе такого запроса будет ровно одна строка, содержащая перечисленные в списке выбора оператора SELECT значения, полученные при обращении к функциям.

Все наши операторы мы вводили руками в диалоговом режиме в подсказке утилиты (ну, если уж быть честным, я-то копировал заранее подготовленные мною тексты из электронного варианта этой книги и помещал их в подсказку утилиты). Если вы допустите какую-либо ошибку, то вам придется заново повторять почти все введенные данные. Утилита sqlcmd имеет параметр -i, который позволяет указать имя файла (файл скрипта), из которого утилита будет читать операторы. Можно поместить пакет операторов в файл, корректировать многократно и выполнять при вызове утилиты sqlcmd. Пример использования файла скрипта:

sqlcmd -i "D:\Ex3-10.sql"

Здесь в параметре -i указывается полный путь к файлу и имя файла скрипта.

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

Пример использования этого параметра:

sqlcmd -o "D:\Result.txt"

Все выходные данные, создаваемые при выполнении утилиты, будут выводиться в файл Result.txt в корневом каталоге на диске D:. Если файл отсутствует на диске, то он будет создан. Если же файл уже существует, то новые строки будут добавляться в конец файла, не изменяя существующих в файле данных.

Напомню, что описание наиболее полезных параметров утилиты sqlcmd содержится в приложении 2.

* * *

Теперь, наконец, создадим в утилите sqlcmd несколько новых пользовательских баз данных. Потом их поудаляем, чтобы затем опять создать, но уже с использованием Management Studio.
Собственно говоря, различные варианты создания баз данных хорошо описаны в документе Books Online. Сейчас мы с вами выполним похожие действия.
Создадим базу данных, где все, что можно, будем устанавливать по умолчанию.
Выполните следующие операторы примера 3.11.

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

USE master;
GO
CREATE DATABASE SimpleDB;
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (12)) AS 'Name',
state AS 'State',
CAST(state_desc AS CHAR(10)) AS 'State desc',
CAST(size AS CHAR(5)) AS 'Size'
FROM sys.master_files
WHERE database_id = DB_ID('SimpleDB');
GO

В результате выполнения оператора CREATE DATABASE будет создана база данных SimpleDB. Все ее характеристики устанавливаются по умолчанию. При выполнении оператора SELECT в данном пакете мы получим следующий список файлов и их характеристик этой базы данных:

ID 
Type
Description
Name
State  
State desc
Size
----------
----------
----------
----------
----------
----------
----------
10ROWS  
SimpleDB
0
ONLINE
520
21LOG
SimpleDB_log
0
ONLINE
130
(2 row(s) affected)

Чаще всего в процессе проектирования баз данных вы будете создавать базу данных и ее объекты, некоторое время с гордостью любоваться результатами вашей деятельности, а затем с грустью замечать, что вы что-то не учли, что-то сделали неверно. Тогда вы начнете вносить изменения в ваши скрипты и вновь запускать их на выполнение. Как правило, база данных пересоздается вами заново. Если вы забудете перед этим удалить уже созданную и не совсем правильную базу данных, то получите сообщение об ошибке. Сейчас я повторно ввел эти же самые операторы из примера 3.11 и получил следующее сообщение:

Msg 1801, Level 16, State 3, Line 1
Database 'SimpleDB' already exists. Choose a different database name.
(Сообщение 1801, уровень 16, состояние 3, строка 1

База данных 'SimpleDB' уже существует. Выберите другое имя базы данных.) 
Чтобы избежать таких неприятностей, настоятельно рекомендую использовать функцию DB_ID(), которую мы с вами уже применяли для определения идентификатора базы данных по ее имени.

В пакетах SQL Server допустимо использование и операторов ветвления, в частности, оператора IF, который, как и в обычных языках программирования, позволяет сделать некоторые проверки и на основании результата таких проверок выполнить различные действия. Сейчас мы его используем для проверки существования нашей базы данных, которую собираемся заново создать. Внесите следующие изменения в ваш пакет создания базы данных SimpleDB (пример 3.12).

Пример 3.12. Создание базы данных с удалением существующей "старой" базы данных

USE master;
GO
IF DB_ID('SimpleDB') IS NOT NULL
DROP DATABASE SimpleDB;
GO
CREATE DATABASE SimpleDB;
GO

В операторе IF мы проверяем при помощи функции DB_ID() существование базы данных SimpleDB. Если база данных существует, то функция вернет целое число — идентификатор этой базы данных, и тогда будет выполнен оператор удаления базы данных: DROP DATABASE.
Если же в системе нет такой базы данных, то функция DB_ID() вернет NULL. В этом случае оператор удаления не будет выполняться.

ЗАМЕЧАНИЕ
Возможность использования операторов ветвления, операторов циклов и некоторых других в пакетах SQL Server является необыкновенно удобным средством. Не все системы управления базами данных имеют такую возможность. Пользуясь случаем, хочу от имени всего прогрессивного человечества поблагодарить корпорацию Microsoft за такое средство.

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

Пример 3.13. Создание базы данных BestDatabase

USE master;
GO
IF DB_ID('BestDatabase') IS NOT NULL
DROP DATABASE BestDatabase;
GO
CREATE DATABASE BestDatabase
ON PRIMARY (NAME = BestDatabase_dat,
FILENAME = 'D:\BestDatabase\Winner.mdf',
SIZE = 5 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1 MB)
LOG ON (NAME = BestDatabase_log,
FILENAME = 'D:\BestDatabase\Winner.ldf',
SIZE = 2 MB,
MAXSIZE = 30 MB,
FILEGROWTH = 1 MB);
GO

Оба файла базы данных — файл данных и файл журнала транзакций — располагаются на диске D: в каталоге BestDatabase. Напомню, что на соответствующем диске каталог с этим именем уже должен существовать, иначе при выполнении оператора создания базы данных вы получите сообщение об ошибке. Сами же файлы с такими именами должны отсутствовать в указанном каталоге.
Для файла данных в создаваемой базе данных установлен начальный размер 5 Мбайт, приращение указано 1 Мбайт, максимальный размер не ограничивается, следовательно, файл может расти до исчерпания объема дискового пространства или до 16 Тбайт.
Начальный размер файла журнала транзакций задается 2 Мбайта, максимальный размер 30 Мбайт, а квант увеличения размера — 1 Мбайт.

МАЛЕНЬКОЕ ЗАМЕЧАНИЕ ПО СИНТАКСИСУ
При указании размеров в операторе CREATE DATABASE единицы измерения могут записываться сразу же после числа, а могут помещаться и через один или более пробелов. Во втором случае запись выглядит, как мне кажется, много лучше. Надеюсь, вы обратили внимание, что во всех примерах единицы измерения заданы явно — там, где можно было бы опустить указание мегабайтов, ключевое слово MB все равно присутствует.
Если у вас еще остаются смутные сомнения в необходимости явного задания величин, для которых можно было бы использовать значения по умолчанию, попробуйте разобраться в скриптах, написанных для других систем управления базами данных, где использованы принятые именно там значения по умолчанию.

Следующий пример в принципе повторяет предыдущий, однако здесь мне хочется рассмотреть некоторые дополнительные полезные средства, используемые в пакетах SQL Server, — локальные переменные и оператор EXECUTE. Выполните следующий пакет (пример 3.14).

Пример 3.14. Создание базы данных BestDatabase, другой вариант

USE master;
GO
IF DB_ID('BestDatabase') IS NOT NULL
DROP DATABASE BestDatabase;
GO
DECLARE @path AS VARCHAR(255),
@path_data AS VARCHAR(255),
@path_log AS VARCHAR(255);
SET @path = 'D:\BestDatabase\';
SET @path_data = @path + 'Winner.mdf';
SET @path_log = @path + 'Winner.ldf';
EXECUTE(
'CREATE DATABASE BestDatabase
ON PRIMARY (NAME = BestDatabase_dat,
FILENAME = ''' + @path_data + ''',
SIZE = 5 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1 MB)
LOG ON (NAME = BestDatabase_log,
FILENAME = ''' + @path_log + ''',
SIZE = 2 MB,
MAXSIZE = 30 MB,
FILEGROWTH = 1 MB);');
GO

В этом примере мы в операторе DECLARE объявляем три локальные переменные, т. е. переменные, используемые только в данном пакете: @path, @path_data и @path_log, указав для них строковый тип данных переменной длины до 255 символов (AS VARCHAR(255)). Имена локальных переменных должны начинаться с символа @.
Затем операторами SET мы присваиваем этим переменным значения, причем для переменных @path_data и @path_log мы используем операцию конкатенации (соединения строк), которая задается символом плюс (+). В результате эти две локальные переменные будут иметь значение полного пути к файлу данных и к файлу журнала транзакций соответственно.

ЗАМЕЧАНИЕ
Существование объявленных локальных переменных ограничено оператором GO. После выполнения этого оператора система уже ничего "не знает" про любые объявленные локальные переменные. Далее в скрипте можно объявлять переменные с теми же именами и с любыми иными типами данных.
Собственно для создания базы данных мы выполняем оператор EXECUTE (для него можно также использовать и сокращение EXEC), которому в качестве параметра передаем строку, которую создаем опять же при выполнении конкатенации строковых констант и значений локальных параметров @path_data и @path_log. Вся строка заключается в апострофы, а параметр оператора помещен в круглые скобки.
Обратите внимание, как здесь определяются имена файлов данных и журнала транзакций:

FILENAME = ''' + @path_data + ''',
...
FILENAME = ''' + @path_log + ''',
...

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

'D:\BestDatabase\Winner.mdf'

и

'D:\BestDatabase\Winner.ldf'

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

Для того чтобы просмотреть и проверить правильность результата формирования такой строки, достаточно в этом пакете только лишь заменить оператор EXECUTE на SELECT. Строка будет отображена на мониторе. Здесь довольно легко можно найти и исправить ошибки. Что я и сделал при написании предыдущего примера, поскольку вначале при создании этой строки допустил ошибку.

Пример 3.14 является не просто демонстрацией некоторых из тех возможностей, которые существуют в SQL Server. Этот прием позволяет в программных пакетах на основании каких-то условий динамически создавать операторы Transact-SQL.
Есть еще один способ динамического создания операторов с использованием утилиты sqlcmd, который мы рассмотрим чуть позже.

Теперь создадим базу данных, содержащую два файла данных и два файла журнала транзакций. По правде сказать, практически ничего нового мы здесь не увидим (пример 3.15). Напомню только, что перед выполнением примера на диске D: нужно создать каталог Multy.

Пример 3.15. Создание многофайловой базы данных

USE master;
GO
IF DB_ID('Multy') IS NOT NULL
DROP DATABASE Multy;
GO
CREATE DATABASE Multy
ON
PRIMARY
( NAME = Multy1,
FILENAME = 'D:\Multy\Multy1.mdf'),
( NAME = Multy2,
FILENAME = 'D:\Multy\Multy2.ndf')
LOG ON
( NAME = MultyL1,
FILENAME = 'D:\Multy\MultyL1.ldf'),
( NAME = MultyL2,
FILENAME = 'D:\Multy\MultyL2.ldf');
GO

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

* * *

Во всех предыдущих примерах создаваемые базы данных имели только одну файловую группу — первичную (PRIMARY), которая обязательно присутствует для каждой базы данных. База данных помимо первичной файловой группы может содержать и произвольное количество других файловых групп, называемых пользовательскими или вторичными файловыми группами.
Теперь создадим базу данных, в которой будет две файловые группы, каждая из которых содержит, скажем, по два файла данных.
Рассмотрим фрагмент синтаксиса оператора CREATE DATABASE, предложение FILEGROUP, относящееся к созданию файловых групп:

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

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

Пример 3.16. Создание базы данных с двумя файловыми группами

USE master;
GO
IF DB_ID('MultyGroup') IS NOT NULL
DROP DATABASE MultyGroup;
GO
CREATE DATABASE MultyGroup
ON
PRIMARY
( NAME = MultyGroup1,
FILENAME = 'D:\MultyGroup\MultyGroup1.mdf'),
( NAME = MultyGroup2,
FILENAME = 'D:\MultyGroup\MultyGroup2.ndf'),
FILEGROUP MultyGroup2
( NAME = MultyGroup3,
FILENAME = 'D:\MultyGroup\MultyGroup3.ndf'),
( NAME = MultyGroup4,
FILENAME = 'D:\MultyGroup\MultyGroup4.ndf')
LOG ON
( NAME = MultyGroupLog1,
FILENAME = 'D:\MultyGroup\MultyGroupLog1.ldf'),
( NAME = MultyGroupLog2,
FILENAME = 'D:\MultyGroup\MultyGroupLog2.ldf');
GO

Вообще-то все основные характеристики файлов данных и журнала транзакций взяты с некоторыми изменениями из примера 3.15. Здесь только добавлена вторичная файловая группа с именем MultyGroup2.

Теперь посмотрим, что у нас в результате получилось. Сначала отобразим файлы созданной базы данных. Обратимся к уже хорошо знакомому нам системному представлению sys.database_files. Выполните оператор примера 3.17.

Пример 3.17. Отображение состояния базы данных с двумя файловыми группами

USE MultyGroup;
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (16)) AS 'Name',
state AS 'State',
CAST(state_desc AS CHAR(10)) AS 'State desc'
FROM sys.database_files;
GO

Будет получен следующий результат:
ID 
Type
Description
Name
State
State desc
-----------
-----------
-----------
-----------
-----------
-----------
10ROWS
MultyGroup1
0ONLINE
21LOG
MultyGroupLog1
0ONLINE
30ROWS
MultyGroup2
0ONLINE
40ROWS
MultyGroup3
0ONLINE
50ROWS
MultyGroup4
0ONLINE
61LOG
MultyGroupLog2
0ONLINE
(6 row(s) affected)

Похожий результат можно получить, как вы помните, и при использовании системного представления sys.master_files.
Теперь отобразите сведения только по файловым группам, используя системное представление sys.filegroups, как это показано в примере 3.18.

Пример 3.18. Отображение файловых групп базы данных MultyGroup

USE MultyGroup;
GO
SELECT CAST(name AS CHAR(12)) AS 'Name',
CAST(type AS CHAR(2)) AS 'Type',
CAST(type_desc AS CHAR(16)) AS 'Descriprion',
CAST(is_read_only AS CHAR(1)) AS 'Read-only'
FROM sys.filegroups;
GO

Результат:
Name
Type 
Descriprion
Read-only
------------
------------
------------
------------
PRIMARY
FG
ROWS_FILEGROUP
0
MultyGroup2
FG
ROWS_FILEGROUP
0
(2 row(s) affected)

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

В завершение использования утилиты командной строки sqlcmd мне хочется показать вам одну возможность применения параметров при выполнении в этой утилите заранее подготовленного скрипта.

Ранее мы сказали несколько слов о том, что при вызове утилиты можно не вводить в диалоговом режиме все нужные операторы, а поместить пакет соответствующих операторов в файл скрипта и при вызове утилиты указать имя этого скрипта при использовании параметра утилиты -i.
Скрипт может содержать параметры, значения которым присваиваются при вызове утилиты. Структура имени параметра следующая:

$(<имя параметра>)

Задание значений параметрам в скрипте выполняется при использовании параметра (или, иными словами, переключателя) утилиты -v. Здесь после имени переключателя указывается имя параметра, знак равенства и в кавычках значение параметра. 

Рассмотрим пример скрипта с параметрами. Подготовим скрипт, содержащий оператор создания простой базы данных. Имена базы данных, файлов и каталогов для хранения файлов базы данных зададим при помощи параметра $(DBNM).
Создайте, например, в Блокноте следующий скрипт:

Пример 3.19. Скрипт создания базы данных, содержащий параметр

USE master;
GO
IF DB_ID('$(DBNM)') IS NOT NULL
DROP DATABASE $(DBNM);
GO
CREATE DATABASE $(DBNM)
ON PRIMARY (NAME = $(DBNM)_dat,
FILENAME = 'D:\$(DBNM)\$(DBNM).mdf',
SIZE = 5 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1 MB)
LOG ON (NAME = $(DBNM)_log,
FILENAME = 'D:\$(DBNM)\$(DBNM).ldf',
SIZE = 2 MB,
MAXSIZE = 30 MB,
FILEGROWTH = 1 MB);
GO
SELECT CAST(file_id AS CHAR(2)) AS 'ID',
CAST(type AS CHAR(4)) AS 'Type',
CAST(type_desc AS CHAR(11)) AS 'Description',
CAST(name AS CHAR (12)) AS 'Name',
CAST(size AS CHAR(5)) AS 'Size'
FROM sys.master_files
WHERE database_id = DB_ID('$(DBNM)');
GO

Сохраните этот скрипт в корневом каталоге на диске D: с именем, например,

CreateDBParam.sql.

Здесь параметр $(DBNM) используется для задания имени базы данных, имени каталога на диске D: для размещения файлов создаваемой базы данных, для задания логических имен файла данных и файла журнала транзакций и для задания имен файлов в операционной системе. При задании путей к файлам, логических и физических имен файлов используется конкатенация, соединение нескольких строк. Причем никаких знаков операции для конкатенации в этом случае не требуется. Параметр $(DBNM) просто будет заменен заданным при вызове утилиты значением.
Создайте на диске D: каталог с именем DBParam. Выполните утилиту в командной строке (предварительно завершив выполнение предыдущего сеанса утилиты, введя оператор quit):

sqlcmd -i "D:\CreateDBParam.sql" -v DBNM="DBParam"

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

ID 
Type
Description
Name
Size
-----------
-----------
-----------
-----------
-----------
10ROWS
DBParam_dat
640
21LOG
DBParam_log
256
(2 row(s) affected)

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

IF DB_ID('$(DBNM)') IS NOT NULL
DROP DATABASE $(DBNM);

В процессе выполнения скрипта этот оператор после подстановки значения параметра будет выглядеть следующим образом:

IF DB_ID('DBParam') IS NOT NULL
DROP DATABASE DBParam;

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

Исходное предложение:

FILENAME = 'D:\$(DBNM)\$(DBNM).mdf',

После подстановки значения эта строка примет вид:

FILENAME = 'D:\DBParam\DBParam.mdf',

Скрипт может содержать произвольное количество параметров. Синтаксис задания им значений при вызове утилиты является довольно свободным. Значения параметрам задаются после переключателя (иногда переключатель называют параметром — здесь не смешивайте терминологию) утилиты -v. Присваивание значений параметрам может следовать после этого переключателя, отделяясь друг от друга пробелами, либо перед каждым присваиванием можно указывать переключатель утилиты -v.

Например, если в скрипте присутствует три параметра: $(P1), $(P2) и $(P3), то значения им можно задать в виде

sqlcmd ... -v P1="V1" P2="V2" P3="V3"

или в следующей форме:

sqlcmd ... -v P1="V1" -v P2="V2" -v P3="V3"

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

* * * 

Надеюсь, вы получили соответствующее удовольствие от работы с утилитой командной строки. В дальнейшем я буду описывать работу с операторами языка Transact-SQL, не привязываясь к средствам реализации. Где вы их будете применять — это ваше решение. Совершенно одинаково (или почти одинаково) эти операторы можно выполнять как при вызове утилиты sqlcmd в командной строке или в PowerShell, так и в компоненте системы с более мощными и удобными в работе возможностями: Management Studio. В Management Studio вам, скорее всего, не потребуется выполнение преобразований CAST(), как мы это делали в предыдущих примерах, отображая сведения о базах данных и их файлах, поскольку этот компонент автоматически определяет размер каждого столбца. Если такие размеры вас не устраивают, то вы легко с помощью мыши можете их изменять.
Сейчас мы перейдем к рассмотрению средств, существующих в Management Studio.

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

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

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