Работа с данными в SQL Server
1.База данных в SQL Server
Сведения обо всех базах данных, относящихся к текущему экземпляру сервера базы данных, хранятся в системной базе данных master. Вы всегда при наличии соответствующих полномочий сможете просмотреть всю обширную информацию, касающуюся любой базы данных текущего экземпляра сервера, а также данные относительно всех файлов, входящих в состав каждой базы данных.
В SQL Server существует два вида баз данных — системные базы данных и пользовательские базы данных.
Системные базы данных создаются автоматически при инсталляции SQL Server и изменяются, как правило, системой. Хотя некоторые изменения в отдельных системных базах данных может выполнить и пользователь, но это следует делать только в том случае, если пользователь точно понимает, что он делает и к каким результатам это может привести. Профессиональные программисты SQL Server часто после инсталляции системы вносят нужные им изменения в базу данных model, которая используется при создании пользовательских баз данных.
Пользовательские базы данных создаются, изменяются и удаляются пользователем. В такие пользовательские базы данных вы помещаете все те данные, которые необходимы вам (вашему заказчику) для решения задач конкретной предметной области. Сюда же можно помещать и другие нужные для работы объекты базы данных — хранимые процедуры, пользовательские типы данных, триггеры, функции и др.
Любая база данных в SQL Server состоит, как минимум, из двух файлов — файла (или нескольких файлов) данных и файла (нескольких файлов) журнала транзакций. Файлы данных (это относится только к файлам данных, но не к файлам журнала транзакций) могут объединяться в файловые группы. Каждому файлу базы данных, будь это файл данных или файл журнала транзакций, присваивается как имя, под которым файл известен в операционной системе, так и логическое имя, по которому к этому файлу можно обращаться в среде SQL Server.
Пользовательская база данных также имеет ряд характеристик, которые можно просматривать и при необходимости изменять с использованием различных средств, существующих в системе.
К базе данных можно обратиться по ее логическому имени, задаваемому явно при создании базы данных. При обращении к некоторым функциям используется идентификатор базы данных — целочисленное значение, присваиваемое системой в момент создания базы данных.
2. Системные базы данных
Системные базы данных содержат сведения, необходимые для работы SQL Server.
Системными базами данных являются: master; model; msdb; tempdb.
База данных master является наиболее важной системной базой данных в SQL Server. Она содержит все данные, необходимые для работы с СУБД. Она также содержит данные о конфигурации сервера базы данных, сведения обо всех пользовательских базах данных, созданных в экземпляре сервера: характеристики баз данных, характеристики и размещение файлов каждой базы данных. Настоятельно рекомендуется выполнять ее резервное копирование при создании, изменении или удалении любой базы данных пользователя. SQL Server не сможет выполняться, если база данных master недоступна. База данных состоит из двух файлов: из файла данных (логическое имя master, имя файла базы данных master.mdf) и файла журнала транзакций (логическое имя mastlog, имя файла mastlog.ldf).
База данных model
Основным назначением базы данных model является хранение шаблонов для всех вновь создаваемых пользователем баз данных. При создании новой пользовательской базы данных в нее из базы данных model копируются типы данных. Создаваемым базам данных присваиваются значения по умолчанию многочисленных характеристик, которые также выбираются из базы данных model. Если вы добавите новые объекты в базу данных model, то эти объекты будут копироваться во все вновь создаваемые пользовательские базы данных текущего экземпляра сервера. База данных состоит из двух файлов: файла данных (логическое имя modeldev, имя файла model.mdf) и журнала транзакций (логическое имя modellog, имя файла modellog.ldf).
База данных msdb
В SQL Server существуют средства создания расписаний (schedule) для автоматического выполнения заданий, ведения истории их выполнения и для выдачи предупреждающих сообщений (alert). Все это хранится в базе данных msdb. Используется в основном компонентом SQL Server Agent. В этой базе данных также хранится история создания резервных копий, SSIS-пакеты, сведения о репликациях. Используется компонентами Service Broker и database mail. Эта база данных состоит из двух файлов: файла данных (логическое имя MSDBData, имя файла MSDBData.mdf) и журнала транзакций (логическое имя MSDBLog, имя файла MSDBLog.ldf).
База данных tempdb
В системной базе данных tempdb хранятся временные объекты, создаваемые пользователями (в первую очередь это временные таблицы, которые существуют только на время выполнения соответствующей программы пользователя, где они были созданы), это внутренние объекты, создаваемые сервером базы данных при выполнении запросов, а также ряд других объектов. Во многих случаях использование базы данных tempdb позволяет повысить производительность системы при выполнении различных операций с базами данных. База данных так же, как и все остальные системные базы данных, использует два файла: файл данных (логическое имя tempdev, имя файла tempdb.mdf) и журнал транзакций (логическое имя templog, имя файла templog.ldf).
База данных resource
В SQL Server существует и такой невидимый обычными средствами объект, как скрытая база данных resource. Эта база данных в схеме sys содержит системные объекты SQL Server (системные хранимые процедуры, представления, функции), которые доступны из любой пользовательской и системной базы данных. Использование ресурсной БД облегчает внесение изменений при установке пакетов исправлений и иных обновлений за счет простой замены на новую версию базы данных. Эта база данных хранится отдельно от всех других системных баз данных. Несмотря на то, что она сильно засекречена, сообщу вам имена ее файлов: файл данных имеет физическое имя mssqlsystemresource.mdf, файл журнала транзакций — физическое имя mssqlsystemresource.ldf.
Не следует пытаться вручную вносить изменения в системные базы данных. Разве что нужно будет выполнять их резервное копирование после соответствующих изменений и при необходимости, в случаях сбоев системы, осуществлять восстановление скопированных баз данных.
3. Базы данных пользователей
В созданной пользователем базе данных хранится множество объектов. Главным объектом являются, разумеется, таблицы, в которые вы помещаете все данные, необходимые для решения задач конкретной предметной области. База данных содержит как сами данные, хранящиеся в таблицах, таки метаданные, описывающие эти данные. Хранение в базе данных и метаданных является важнейшим принципом, применяемым во всех базах данных. Это позволяет уменьшить зависимость программ от структуры базы данных.
Помимо пользовательских объектов в базе данных хранятся системные объекты — системные таблицы, системные представления (их очень большое количество), системные хранимые процедуры (их еще больше), системные функции , системные типы данных, а также пользователи, роли, схемы.
База данных может содержать не более 32 767 файлов для хранения данных. Первый или единственный файл данных называется первичным файлом. И при вновь созданной, так сказать "пустой", базе данных в первичном файле хранятся системные данные, такие как ссылки на другие, вторичные файлы данных и на файлы журнала транзакций. Начальный размер первичного файла не может быть меньше, чем 3 Мбайта.
При желании вы можете использовать вторичные файлы для хранения данных. Во вторичных файлах хранятся только пользовательские данные. Файлы данных могут объединяться в файловые группы. В любой базе данных всегда присутствует первичная файловая группа PRIMARY. Если не создано никакой вторичной файловой группы, то все файлы данных принадлежат первичной группе. В некоторых случаях имеет смысл объединять отдельные файлы в файловые группы с целью повышения производительности системы. В файловые группы могут объединяться только файлы данных, но не файлы журнала транзакций.
Журнал транзакций также может быть представлен несколькими файлами. В журнале хранятся все изменения базы данных, выполненные в контексте каждой транзакции. Прежде чем записать выполненные пользователем изменения в файл данных, система вначале осуществляет необходимые записи в журнал транзакций.
Журнал используется для выполнения операций подтверждения (COMMIT) или отката (ROLLBACK) транзакций, а также для целей восстановления базы данных на любой заданный момент времени или в случае ее разрушения.
4. Характеристики базы данных
1. Владелец базы данных (Owner) имеет все полномочия к базе данных. Он может изменять характеристики базы, удалять ее, вносить любые изменения в данные и метаданные. Владельцем базы становится пользователь, создавший базу данных.
Владельца пользовательской базы данных можно изменить, используя в языке Transact-SQL системную процедуру sp_changedbowner. Вот несколько упрощенный синтаксис обращения к этой процедуре:
EXECUTE sp_changedbowner '<имя нового владельца>'
Функция возвращает значение 0 при успешной смене владельца или 1 в случае возникновения ошибки. Имя нового владельца уже должно быть описано в системе.
Чтобы получить список существующих в системе пользователей, можно выполнить системную хранимую процедуру sp_helplogins:
EXEC sp_helplogins;
2. Порядок сортировки (collation) для базы данных определяет допустимый набор символов в строковых типах данных CHAR, VARCHAR и правила, по которым будут при необходимости упорядочиваться эти строковые данные. Порядок сортировки определяет, будет ли сортировка происходить по внутреннему коду или в лексикографическом (алфавитном) порядке, в каком порядке будут размещаться строчные и прописные буквы, как распределяются знаки препинания, иные специальные символы и др. Если для строкового типа данных явно не указан порядок сортировки, то ему будет присвоен порядок, заданный по умолчанию для всей базы данных. Для элемента данных при его описании в базе можно указать любой допустимый порядок сортировки, отличный от порядка сортировки базы данных.
3. Возможность изменения данных базы данных
База данных может находиться в состоянии только для чтения (READ_ONLY) или доступна как для чтения, так и для внесения изменений в данные (READ_WRITE).
4. Состояние базы данных (Database State)
В каждый момент времени любая база находится в одном конкретном состоянии (state). В SQL Server существуют следующие состояния базы данных:
- ONLINE. База данных в доступном состоянии. С ней можно выполнять любые действия по изменению данных и метаданных. В этом состоянии средствами операционной системы невозможно удалить или даже скопировать файлы базы данных на другие устройства (при запущенном на выполнение сервере базы данных).
- OFFLINE. База данных в недоступном состоянии (автономном режиме). Никакие действия с объектами базы данных в этом состоянии невозможны. Однако средствами операционной системы можно удалить файлы базы данных, чего делать все-таки не стоит, или скопировать их на другой носитель.
- RESTORING. База данных недоступна. В это состояние она переводится, когда выполняется восстановление файлов данных из резервной копии.
- RECOVERING. База данных недоступна, она находится в процессе восстановления. После завершения восстановления база автоматически будет переведена в оперативное состояние (ONLINE).
- RECOVERY_PENDING. Это состояние ожидания исправления ошибок восстановления базы данных. База данных недоступна. В процессе восстановления базы произошла ошибка, которая требует вмешательства пользователя. После исправления ошибки пользователь сам должен перевести базу в оперативное состояние.
- SUSPECT. База данных недоступна. Она помечена как подозрительная и может быть поврежденной. Со стороны пользователя требуются действия по устранению ошибок.
- EMERGENCY. База данных повреждена и находится в состоянии только для чтения (READ_ONLY). Такое состояние базы используется для ее диагностики и при попытках скопировать неповрежденные данные.
- DEFUNCT. Файл был удален (разумеется, когда он не был в состоянии ONLINE).
Если при работе с базой данных отдельные файлы являются недоступными, то все же возможны различные операции с базой данных, если для выполнения этих операций требуются только данные, содержащиеся в файлах, которые находятся в оперативном режиме. Это одно из неоспоримых преимуществ системы MS SQL Server.
| 
