Работа в среде MS SQL Server 2005

64 Pages·0.525 MB·Russian

by  Ярош, Е. С.

Министерство образования Российской Федерации
Южно-Уральский государственный университет
Кафедра “Электронные вычислительные машины”

681.3.06(07)
Я77

Ярош Е.С.

РАБОТА В СРЕДЕ MS SQL SERVER 2005
Учебное пособие к лабораторным работам

Челябинск
Издательство ЮУрГУ
2009
УДК 681.3.06(07)
Я77

Одобрено
учебно-методической комиссией
приборостроительного факультета

Рецензенты:
М.Ю. Катаргин, Л. А. Полякова

Ярош Е.С.
Я77 Работа в среде MS SQL Server 2005: Учебное пособие к лабораторным
работам. – Челябинск: Изд. ЮУрГУ, 2009. – 64 с

ISBN 5-696-00000-0
Пособие предназначено, в первую очередь, для студентов специальности
230101 − "Вычислительные машины, комплексы, системы и сети". Оно
поддерживает курс "Базы данных", изучаемый в восьмом семестре, в части
рассмотрения работы СУБД с архитектурой клиент-сервер. Материал может
быть полезен также специалистам других направлений при необходимости
освоения программного продукта Microsoft SQL Server 2005.
УДК 681.3.06(07)

ISBN 5-696-00000-0 © Издательство ЮУрГУ, 2009
2
ВВЕДЕНИЕ

SQL Server фирмы Microsoft (MS SQL Server) является одной из наиболее
известных систем управления базами данных (СУБД). Наряду с Oracle и DB2 он
занимает лидирующее положение на рынке СУБД. Традиционный для
программных продуктов фирмы Microsoft дружественный интерфейс, простота и
удобство обслуживания успешно сочетаются в MS SQL Server с высокой
производительностью, наличием всех возможностей современных СУБД.
Привлекательным фактором является также невысокая стоимость MS SQL
Server по сравнению с конкурирующими СУБД. Оптимальное сочетание цены и
качества делает этот программный продукт очень популярным.
Настоящее пособие предназначено для практического освоения базовых
приемов работы с MS SQL Server 2005. Оно состоит из тематических разделов,
позволяющих последовательно ознакомиться с основными задачами,
возникающими в процессе эксплуатации этой системы.
Первый раздел является ознакомительным. Он дает краткое представление о
MS SQL Server в целом.
Второй раздел посвящен созданию базы данных. Здесь рассматриваются
общие вопросы организации и хранения данных в MS SQL Server, возможности
пользователя по проектированию собственных информационных структур.
Третий раздел рассматривает вопросы создания представлений и
манипулирования данными.
Четвертый раздел знакомит с программированием сервера, созданием
хранимых процедур, триггеров базы данных и пользовательских функций.
Пятый раздел посвящен системе безопасности MS SQL Server, средствам
ограничения и разграничения доступа к данным различных категорий
пользователей.
Шестой раздел рассматривает важнейшие вопросы настройки и
администрирования сервера, оптимизации производительности, изоляции и
блокировки, позволяющие многим пользователям одновременно работать с базой
данных без отрицательного взаимовлияния, мониторинг и аудит текущей работы.
Седьмой раздел позволяет ознакомиться с организацией двухстороннего
обмена данными между MS SQL Server и другими СУБД.
Известно, что успешное освоение любого программного продукта возможно
только при непосредственной работе за компьютером. Однако нажатие на
клавиши без понимания сути происходящих в системе процессов мало
продуктивно. Поэтому для каждой из рассматриваемых задач сначала излагаются
основополагающие принципы, затем следует описание приемов работы, после
чего дается практическое задание.
В каждом разделе рассматривается несколько логически связанных задач.
Разделы и задачи выстроены в последовательности, которая наиболее часто
встречается на практике. Такая последовательность обеспечивает также
наилучшее усвоение материала. Поэтому рекомендуется выполнять все задания
без нарушения порядка их следования.
3
1. ЗНАКОМСТВО С MS SQL SERVER

MS SQL Server работает на Windows-платформе фирмы Microsoft. В настоящее
время базовой является версия MS SQL Server 2005. Этот продукт выпускается в
следующих редакциях:
• Enterprise Edition (32-разрядная и 64-разрядная) – самая полная редакция,
СУБД масштаба предприятия, позволяющая создавать крупные
информационно-аналитические системы, работающие на отказоустойчивых
кластерах. Количество процессоров не ограничено, размер оперативной
памяти лимитируется операционной системой;
• Standard Edition (32-разрядная и 64-разрядная) – СУБД для среднего и малого
бизнеса, поддерживает работу с количеством процессоров до 4 и объемом
оперативной памяти, ограниченным возможностями операционной системы;
• Workgroup Edition (32-разрядная) – СУБД масштаба рабочих групп,
соответствующая потребностям небольших организаций, легко обновляется
до стандартного и корпоративного выпуска. Поддерживает работу с
количеством процессоров до 2 и объемом оперативной памяти до 3 Гб;
• Developer Edition (32-разрядная и 64-разрядная) – вариант поставки,
предназначенный для разработчиков приложений на основе MS SQL Server.
Он включает все функции Enterprise Edition, но по условиям лицензионного
соглашения может использоваться исключительно в целях разработки и
тестирования; коммерческое использование запрещено;
• Express Edition (32-разрядная) – бесплатная СУБД, может передаваться (в
соответствии с соглашением) третьим лицам. Предназначена для малых
производителей программного обеспечения, пользователей серверов низшего
уровня, непрофессиональных разработчиков, создающих веб-приложения, и
любителей, создающих клиентские приложения. Число процессоров – 1,
объем оперативной памяти до 1 Гб, размер базы данных – до 4 Гб.
Как и многие серверные продукты, работающие под управлением системы
Windows, MS SQL Server реализован в виде набора служб операционной системы,
каждая из которых запускается самостоятельно и отвечает за определенный круг
задач.
Основные компоненты сервера:
• SQL Server Database Engine – основная служба для хранения, обработки и
обеспечения сохранности данных, репликации, полнотекстового поиска и
управления реляционными и XML-данными. Это ядро системы,
выполняющее все основные операции: регистрация пользователей и
контроль их доступа к ресурсам, установление соединения, обслуживание
обращений к данным, контроль использования системных ресурсов и др. Все
остальные службы можно рассматривать как расширения службы SQL Server
Database Engine, добавляющие гибкость и функциональность. Служба SQL
Server Database Engine всегда запускается первой;
4
• Analysis Services – набор служб для создания и управления приложениями
интерактивной аналитической обработки (OLAP) и приложениями
интеллектуального анализа данных;
• Reporting Services – набор служб, включающий серверные и клиентские
компоненты для создания отчетов в табличной, матричной, графической и
свободной форме, управления ими и их развертывания. Службы Reporting
Services также представляют собой расширяемую платформу, которую
можно использовать, чтобы разрабатывать приложения отчетов. Для
установки служб Reporting Services требуются службы IIS версии 5.0 или
выше, для конструктора отчетов служб Reporting Services требуется Internet
Explorer версии 6.0 с пакетом обновления SP1;
• Notification Services – платформа для разработки и развертывания
приложений, посылающих персонифицированные, актуальные сведения
подписчикам на различных устройствах;
• Integration Services – набор графических средств и программируемых
объектов для перемещения, копирования и преобразования данных.

Поставляются также клиентские компоненты, устанавливающие связи между
клиентами и серверами, и сетевые библиотеки для DB-library, ODBC и OLE DB.
Продукты от Microsoft всегда славились удобными и мощными средствами
управления. В MS SQL Server к ним относятся:
• SQL Server Management Studio – интегрированная среда для доступа ко всем
компонентам SQL Server, управления ими, их настройки, разработки и
администрирования. SQL Server Management Studio объединяет возможности
программ Enterprise Manager (средство администрирования сервера), Query
Analyzer (ввод и выполнение SQL-утверждений, показ плана выполнения
запроса, настройка и исследование ряда характеристик запроса) и Service
Manager (показ состояния сервера, управление сервером), входивших в
состав прежних выпусков SQL Server. Единая среда обеспечивает доступ к
SQL Server разработчикам и администраторам с любым уровнем подготовки.
Для установки SQL Server Management Studio требуется Internet Explorer
версии 6.0 с пакетом обновления SP1;
• Диспетчер конфигурации SQL Server – обеспечивает базовые возможности
управления конфигурациями для служб, серверных протоколов, клиентских
протоколов и псевдонимов клиентов SQL Server;
• SQL Server Profiler – обеспечивает графический интерфейс пользователя для
контроля над экземпляром компонента Database Engine или экземпляром
служб Analysis Services;
• Помощник по настройке ядра СУБД – помогает создавать оптимальные
наборы индексов, индексированных представлений и секций.
Средство разработки Business Intelligence Development Studio представляет
собой интегрированную среду разработки для решений служб Analysis Services,
Reporting Services и Integration Services. Для установки Business Intelligence
5
Development Studio требуется Internet Explorer версии 6.0 с пакетом обновления
SP1.
В комплект поставки входит электронная документация и образцы кода и
приложений для компонента Database Engine и служб Analysis Services, Reporting
Services и Integration Services.

Запуск сервера можно выполнить:
1. Из Sql Server Management Studio. При вызове этой среды первым появляется
окно, в котором выбирается нужный сервер и его компонент;
2. Из командной строки (каталог MSSQL.1\BINN)
sqlservr – d <drive>:\<directory>\DATA\master.mdb,
где – d – ключ, определяющий путь (с указанием устройства <drive>: ) к главной
базе master.mdb.
Можно задать и другие ключи, создать ярлык для запуска сервера.
2. РАБОТА № 1.
БАЗЫ ДАННЫХ И ЖУРНАЛЫ ТРАНЗАКЦИЙ

База данных MS SQL Server состоит из нескольких файлов:
• *.mdf – основной файл базы данных, для каждой базы может быть только
один (сервером поддерживается до 32767 баз данных);
• *.ndf – дополнительные файлы базы данных, не обязательны (сервером
поддерживается до 32767 файлов);
• *.ldf – файл журнала транзакций.

2.1. Создание базы данных

База данных может создаваться из среды Management Studio или командой
CREATE DATABASE … языка Transact SQL. Наиболее удобен первый способ.

2.1.1. Создание базы данных из среды Management Studio

1. Выбрать сервер.
2. Активизировать папку Базы данных (Databases) и выбрать в контекстном меню
операцию создания.
3. Задать имя базы данных, имена и местоположение файлов базы данных. С
именем первого файла данных будет связано расширение .mdf, с именами
последующих файлов данных – .ndf, все файлы журнала транзакций получат
расширение .ldf.
4. Задать параметры приращения дискового пространства базы данных.

2.1.2. Свойства базы данных

Раздел Файлы аналогичен режиму создания базы данных.
6
Раздел Файловые группы позволяет создавать имена файловых групп, к
которым можно отнести один или несколько файлов базы данных или журнала
транзакций. Если файловая группа не пуста, она может быть переведена в режим
Read-Only или сделана доступной по умолчанию. В файловой группе по
умолчанию размещаются все данные, для которых не указана файловая группа.
Раздел Параметры позволяет указать режимы работы и параметры,
используемые по умолчанию.
Модель восстановления (Recovery model) управляет регистрацией транзакций
и созданием резервных копий и, следовательно, определяет возможности
восстановления при авариях. Простая модель (Simple) регистрирует минимум
данных о большинстве транзакций и выполняет усечение журнала транзакций
после каждой контрольной точки. Поэтому при восстановлении можно
использовать только резервную копию данных. Резервное копирование и
восстановление журнала транзакций не поддерживается. При полной модели
(Full) в журнале транзакций регистрируются все операции, усечение журнала
транзакций не выполняется. Это позволяет восстановить базу на момент,
непосредственно предшествующий сбою. Модель с неполным
протоколированием (Bulk-Logged) предусматривает минимальную регистрацию
массовых операций, например, работу с большими блоками данных типа Text и
другими объемными объектами с помощью команд Select into / bulk copy и т.д.
Остальные транзакции протоколируются полностью. Если в резервной копии
журнала содержится какая-либо массовая операция, то база данных может быть
восстановлена только до состояния, соответствующего концу резервного файла
журнала, а не к определенному моменту времени и не к помеченной транзакции
внутри резервной копии журнала. Эта модель восстановления используется для
больших массовых операций.
Автоматическое закрытие (AUTO_CLOSE) базы данных с освобождением
используемых ресурсов происходит по завершению работы последнего
пользователя. База данных автоматически открывается снова, когда пользователь
пытается подключиться к ней. При отказе от автоматизации база данных остается
открытой и после отключения последнего пользователя.
Автоматическое создание статистики, автоматическое обновление статистики
(АUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS) означает, что
любая статистика, требуемая для оптимизации запроса, при ее отсутствии
автоматически строится и в ходе работы автоматически обновляется. При отказе
от автоматизации статистику можно создавать/обновлять вручную.
Автоматическое сжатие (AUTO_SHRINK) файлов данных выполняется во
время периодических проверок на неиспользуемое пространство. Файлы журнала
транзакций сжимаются только в том случае, если выбрана простая модель
восстановления базы данных или была создана резервная копия журнала. При
отключении параметра файлы баз данных во время периодических проверок на
неиспользуемое пространство не будут сжиматься автоматически.
Проверка страниц используется для обнаружения незавершенных транзакций
ввода-вывода, вызванных ошибками ввода-вывода диска, и уведомления о таких
7
транзакциях. Возможные значения: None (отключение), TornPageDetection
(обнаружение “оторванных”, т.е. не полностью записанных страниц базы данных)
и Checksum (проверка с помощью контрольного суммирования).
Параметры курсоров настраивают поведение курсоров. Указывается, будет ли
курсор закрываться после фиксации транзакции, открывшей этот курсор. Если
значение равно True, закрываются все курсоры, открытые при фиксации или
откате транзакции. Если значение равно False, при фиксации транзакции курсоры
остаются открытыми, при откате закрываются все курсоры, за исключением
определенных как INSENSITIVE или STATIC. По умолчанию курсоры
объявляются как GLOBAL. Можно изменить эту установку на LOCAL.
Автоокругление чисел задает способ обработки ошибок округления базой
данных. Если значение равно True, формируется ошибка, когда в выражении
происходит потеря точности. Если значение равно False, потери точности не
приводят к формированию сообщений об ошибках, а результат округляется до
степени точности столбца или переменной, в которых сохраняется результат.
ANSI NULL по умолчанию задает поведение операторов сравнения Equals (=)
и Not Equal to (<>) при их использовании со значениями NULL. Если значение
равно True, всем сравнениям со значениями NULL присваивается значение
UNKNOWN. Если значение равно False, сравнения значений, отличных от
Юникода, со значениями NULL получают значение True, если оба они равны
NULL. Чтобы сценарий работал правильно вне зависимости от настроек базы
данных ANSI NULLS или использования директивы SET ANSI_NULLS, следует
использовать IS NULL и IS NOT NULL для сравнений, операнды которых могут
содержать значения NULL.
Включено заполнение ANSI контролирует способ хранения в столбце значений
короче, чем определенный размер столбца, и способ хранения в столбце значений,
имеющих замыкающие пробелы, в данных char, varchar, binary и varbinary. При
включенной установке конечные пробелы в символьных полях и конечные нули в
двоичных полях не усекаются.
Включены предупреждения ANSI задает поведение в соответствии со
стандартом SQL-92 для некоторых состояний ошибки. Если значение равно True,
формируется предупреждающее сообщение, если в статистических функциях
(таких, как SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP или COUNT)
появляются значения NULL. Если значение равно False, предупреждающее
сообщение не выдается.
Включено прерывание при делении на ноль разрешает аварийное прерывание
арифметических действий. Если значение равно True, ошибка переполнения или
деления на ноль приводит к прерыванию выполнения запроса или пакета. Если
произошла ошибка в транзакции, для этой транзакции выполняется откат. Если
значение равно False, выводится предупреждающее сообщение, но запрос, пакет
или транзакция продолжают выполняться, как если бы ошибки не произошло.
Сцепление со значением NULL дает NULL задает способ конкатенации
значений NULL. Если значение свойства равно True, строка + значение NULL
возвращает NULL. Если значение равно False, результатом является строка.
8
Включены заключенные в кавычки идентификаторы регулирует использование
двойных кавычек в идентификаторах. При включенной опции двойные кавычки
используются для идентификаторов (имена таблиц, полей и др.) и аналогичны
квадратным скобкам. Это соответствует правилам ANSI. Можно также
использовать ключевые слова SQL Server как идентификаторы (имена объектов
или переменных), если они заключаются в кавычки. При выключенной опции
двойные кавычки аналогичны одинарным и используются в символьных строках.
Включены рекурсивные триггеры позволяет запускаться триггерам другими
триггерами. Если значение равно True, рекурсивный запуск триггеров разрешен.
Если значение равно False, запрещается только прямая рекурсия. Чтобы
отключить косвенную рекурсию, параметру сервера вложенные триггеры с
помощью процедуры sp_configure надо присвоить значение 0.
Состояние задает режим использования базы данных: только для чтения (Read
only) − немодифицируемая база данных, ограничение доступа (Restrict access) −
средство ограничения доступа к базе данных. Значение Multiple соответствует
обычной многопользовательской работе, Single – однопользовательский режим,
Restricted позволяет работать с базой только пользователям с полномочиями
владельца базы (dbo – data base owner), создателя базы (dbcreator) или системного
администратора (sysadmin). Здесь же показывается текущее состояние базы
данных: нормальная работа, отключение, один из видов восстановления и т.д.
Остальные разделы свойств посвящены различным аспектам обеспечения
безопасности (см. работу № 4).
Задание.
1. Создать базу данных.
2. Изучить ее свойства.
3. Используя справочную систему, ознакомиться с оператором CREATE
DATABASE …, сопоставить его параметры и экранные формы Enterprise
Manager для создания базы данных.
4. Используя справочную систему, ознакомиться с операторами удаления базы
данных DROP DATABASE и корректировки базы данных ALTER DATABASE.

2.2. Типы полей

2.2.1. Базовые типы

Числовые типы.
Целые: integer − 4 б, smallint − 2 б, tinyint − 1 б, bigint −8 б.
С плавающей точкой: real − 4 б, float (double precision) − 8 б.
С фиксированной точкой: decimal (numeric) − от 5 до 17 б, decimal[(p[,s])]
Символьные типы.
Фиксированной длины: char [(n)], n <= 8000.
Переменной длины: varchar [(n)] − пробелы не хранятся, n <= 8000.
Переменной длины в unicode: nchar [(n)], nvarchar [(n)], n − national, 1 символ
кодируется 16 битами, n <= 4000
9
С целью обеспечения соответствия стандарту SQL-2003 для varchar используются
синонимы char varying или character varying.
Текст: text, ntext(n) − блоки по 8 Кб, связный список длиной соответственно до 2
Гб.
Двоичные типы.
Фиксированной длины: binary [(n)], n <= 8000.
Переменной длины: varbinary [(n)], n <= 8000.
Символьный или бинарный образ: image длиной до 2 Г.

Указатель длины max для символьных и двоичных данных переменной длины
означает, что максимальный размер хранения равен 2^31-1 байт (2 Г). Размер
хранения равен фактической длине данных плюс два байта. Введенные данные
могут иметь длину 0.

Дата/время.
Стандартной длины: datetime − 8 б, от 01.01.1753 до 31.12.9999, точность 1/300
сек, первые 4 б − до или после базовой даты 01.01.1900 (< 0 − до, > 0 − после),
вторые 4 б − время в мс от полуночи. В операторах SQL значения типа datetime
заключаются в одинарные кавычки.
Половинной длины: smalldatetime − 4 б, от 01.01.1900 до 06.06.2079, первые 2 б −
дни, вторые 2 б − количество минут после полуночи. Ограничение 01.01.1753
объясняется тем, что в этот день были синхронизированы Григорианский и
Юлианский календари, отличавшиеся до этого на 11 дней. Для хранения ранних
дат следует использовать символьные или числовые типы данных.
Денежные типы.
Стандартной длины: money − 8 б.
Половинной длины: smallmoney − 4 б.
Специальные типы данных (Transact SQL).
bit − 0 | 1 | NULL, в одном байте упаковывается несколько битовых столбцов (от 1
до 8).
timestamp − 8 б, столбец уникален в пределах базы, автоматически обновляется
значением некоторого внутреннего счетчика SQL Server при любых операциях
вставки/изменения записи. Используется для выявления изменений в записи, из-за
нестабильности не используется как первичный ключ. Трактовка этого типа
данных в MS SQL Server не соответствует стандарту SQL-92 и более поздним. По
стандарту тип данных timestamp эквивалентен типу данных datetime. В
дальнейшем предполагается несоответствие устранить. Для обеспечения
совместимости введен синоним rowversion, который рекомендуется использовать
при создании таблиц. Последнее присвоенное значение timestamp можно
получить с помощью системной глобальной переменной @@DBTS:
SELECT @@DBTS
uniqueidentifier − 16 б, 16-ричное число, глобальный уникальный идентификатор
(GUID). Для генерации значений используется номер сетевой карты, уникальный
10