Microsoft SQL Server
Работа добавлена: 2015-12-23





37

Лекция 18. Microsoft SQL Server

Microsoft SQL Server представляет собой СУБД, обеспечивающую создание информационных систем с архитектурой “клиент-сервер”, в которой он играет роль сервера баз данных. SQL Server удовлетворяет требованиям, предъявляемым к системам распределенной обработки информации. Эта СУБД поддерживает: тиражирование данных, параллельную обработку, создание и обработку больших баз данных на недорогих аппаратных платформах, отличается простотой управления и использования.

18.1. Характеристика SQL Server

Нами рассматривается SQL Server версии 7.0. В сравнении с другими версиями продукта в ней имеется достаточно большое число нововведений. Рассмотрим основные понятия и характеристику SQL Server.

SQL Server добавляет к сетевым компонентам специальные элементы сервиса, такие как OLE DB (Object Linking and Embedding-Database - связывание и внедрение объектов базы данных) и ODBC (Open Database Connectivity - совместимость открытых баз данных). С их помощью обеспечивается совместимость различных клиентских приложений при работе с сервером.

Работа SQL Server 7.0 (для варианта, отличного от Desktop - настольного) ведется под управлением операционной системы Windows NT Server, размещенной на компьютере-сервере. Каждый пользователь получает доступ к Windows NT и приложению SQL Server с помощью персонального компьютера-клиента.

На компьютере-клиенте устанавливают одну из операционных систем Windows 9х, DOS, OS/2 или Windows NT Workstaion. Эти операционные системы позволяют запускать приложения независимо от компьютера-сервера. Кроме того, каждый пользователь компьютера-клиента с помощью сетевых средств своей операционной системы может устанавливать связь с компьютером-сервером, работающим под управлением Windows NT Server. На компьютерах-клиентах размещаются также локальные базы данных, работа с которыми ведется с помощью персональных СУБД, например Access или Visual FoxPro. С их помощью осуществляется доступ к базам данных, размещенным на сервере.

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

Рабочая группа (workgroup) представляет собой логическое объединение нескольких компьютеров сети по некоторому признаку (отделам, задачам и т.п.). Объединение компьютеров пользователей в группы выполняется для облегчения совместного использования ресурсов.

В SQL Server 7.0 вместо рабочей группы используется понятие роли (role). Ранее пользователь мог принадлежать одной рабочей группе, что являлось заметным ограничением. Теперь каждому пользователю может быть назначено произвольное число ролей. Например, пользователю может быть назначена роль администратора.

Отметим, что в SQL Server 7.0 серверы могут включаться в серверные группы (server groups), которые обеспечивают способ объединения большого числа серверов в несколько удобно управляемых групп.

Названные понятия играют важное значение в обеспечении безопасности хранения и доступа к информации.

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

SQL Server имеет следующие варианты поставки:

 Enterprise (Предприятий);

 Standard (Стандартный);

  Desktop (Настольный).

SQL Server для установки предъявляет следующие требования к аппаратному обеспечению:

 процессор DEC Alpha-совместимый или Intel-совместимый (Pentium 166, PRO, II и выше);

 основная память: 64 Мбайта - для варианта Enterprise и 32 Мбайта - для остальных вариантов;

 память на диске объемом: 170 Мбайтов для типичной установки, 65 Мбайтов -для минимальной и 190 Мбайтов - для полной;

 накопитель CD ROM.

Вариант Enterprise программы SQL Server работает под управлением операционной системы Windows NT Senver Enterprise Edition 4.0. Вариант Standard программы SQL Server может работать под управлением Windows NT Server 4.0. Вариант Desktop программы SQL Server работает под управлением Windows NT Workstation 4.0 и Windows 9x.

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

18.2. Язык запросов Transact SQL

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

Основные операторы INSERT, DELETE, UPDATE и SELECT и другие операторы имеют общий синтаксис языка SQL для выполнения операций над данными. Дополнительные возможности Transact-SQL в основном связаны с управлением потоками информации и позволяют определять порядок выполнения операторов.

Операторы Transact-SQL можно задавать с помощью утилиты ISQL (Interactive Structured Query Laguage - интерактивный язык структурированных запросов), работающей под управлением MS DOS. Версия ISQL для Windows называется анализатором запросов (Query Analyzer).

В сеансе MS DOS утилиту ISQL можно вызвать с помощью одноименной команды. В командной строке запуска ISQL можно использовать параметры. Например, можно ввести имя пользователя и пароль:

ISQL /Usa /Р<пароль> /S<cepвep>

1>

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

Утилита Query Analyzer позволяет работать с операторами Transact-SQL под управлением Windows. При этом команды Transact-SQL вводятся в отдельном подокне запроса главного окна утилиты Query Analyzer. С помощью этой утилиты можно удобно копировать, вырезать, вставлять, редактировать, сохранять и печатать ранее созданные запросы.

После запуска Query Analyzer требуется подключиться к базе данных SQL Server с указанием имени пользователя, пароля (при необходимости) и используемого сервера. Операторы Transact-SQL вводятся в подокне Query (Запрос). Результаты выполнения запроса отображаются на вкладке Results (Результаты) в нижней части диалогового окна программы.

18.3. Системные базы данных и таблицы

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

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

Глобальные временные таблицы существуют до завершения всех использующих их сеансов.

Журнал транзакций представляет собой рабочую область, в которую SQL Server записывает информацию до и после выполнения транзакций. Эта информация может использоваться для отмены выполненной транзакции или для восстановления БД. В предыдущих версиях SQL Server журнал транзакций размещался в таблице, именуемой системным журналом syslog. Теперь журнал транзакций размещается в отдельном файле, создаваемом автоматически при создании базы данных.

Одной из важнейших новых возможностей SQL Server 7.0 является улучшенная управляемость. Теперь при добавлении данных файлы базы данных и журнала транзакций расширяются автоматически.

Для хранения баз данных используются следующие три типа файлов:

 основной (Primary) файл создается один и содержит информацию, требуемую для инициализации;

  вспомогательные (Secondary) файлы содержат данные, не умещающиеся в основном файле; использование их не обязательно, но позволяет разместить БД на нескольких носителях;

 файлы журналов транзакций хранят информацию для восстановления БД.

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

Для хранения данных используются таблицы, размещаемые в базах данных. В Microsoft SQL Server базы данных делят на два типа — системные и пользовательские. В системных базах данных размещаются метаданные, используемые для управления системой. При инсталляции Microsoft SQL Server создаются следующие системные базы данных: master, model, tempdb и msdb.

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

 учетные записи пользователей;

 сведения о текущих процессах;

 сообщения о системных ошибках;

 сведения о базах данных на сервере;

 выделенные размеры баз данных;

 сведения об активных блокировках;

 сведения о доступных устройствах баз данных и резервных;

 процедуры системного администрирования.

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

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

 типы данных, определяемые пользователями;

 правила проверки ввода;

 значения по умолчанию;

 хранимые процедуры;

 информация о пользователях, которым разрешается доступ к базам данных;

 разрешения, записываемые по умолчанию в учетные записи гостей.

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

Содержимое базы данных tempdb, кроме глобальных временных таблиц, удаляется при разрыве пользователем соединения с SQL Server. При завершении работы с SQL Server из этой базы удаляются все данные. В базе данных tempdb размещаются временные таблицы всех БД, с которыми ведется работа.

Системная база данных msdb используется для обеспечения работы планировщика — службы SQL Executive. Эта служба поддерживает репликацию, планирование задач и управление ошибочными ситуациями.

Кроме системных таблиц, образующих каталог баз данных (database catalog), в базе данных msdb содержатся следующие системные таблицы: sysalerts, sysoperators, sysnotifications, systask, syshistory и sysservermessages. Эти таблицы соответственно содержат информацию о: определенных пользователем событиях; операторах; связях операторов с событиями; определенных пользователем задачах и запускающих задачи событиях; хронологии запуска событий и задач;

сообщениях серверу.

Каталог баз данных (database catalog) представляет собой совокупность 18 системных таблиц, которые имеются в каждой базе данных. Имена всех системных таблиц начинаются с приставки sys, например: sysalternates, syscolumns, syscomments, В них хранится информация о пользователях, триггерах и хранимых процедурах, таблицах, индексах в таблицах, правах доступа пользователей, типах данных, ограничениях, публикациях, репликацих и другах. Наименования системных таблиц мнемонически указывают на характер хранимой информации. Например, в системной таблице sysindexes содержатся данные об индексах.

Системный каталог, или словарь данных, представляет собой совокупность 13 системных таблиц, размещенных в базе данных master. В состав системного каталога входят следующие системные таблицы: syscharsets, sysconfigures, syscurconfig, sysdatabases, sysdevices, syslanguages, syslocks, syslogin, sysmessages, sysprocesses, sysremotelogins, sysservers, sysusages.

Заметим, что информация о том, какие объекты находятся в базе данных, размещается в системной таблице sysobject, имеющейся в каждой базе данных.

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

При работе с базами данных, размещенными на сервере, можно выделить следующие этапы:

 создание базы данных и таблиц;

 создание представлений и хранимых процедур;

 работа с таблицами;

 восстановление данных;

 администрирование.

В среде Microsoft SQL Server названные этапы можно реализовать на языке Transact-SQL.

Создание базы данных может быть выполнено пользователем при наличии у него соответствующих полномочий, устанавливаемых системным администратором. Для создания базы данных можно использовать SQL Server Enterprise Manager или команду Transact-SQL CREATE DATABASE. Напомним, что при создании базы данных в качестве шаблона используется база данных model, содержащая ряд системных таблиц.

Рис. 18.1. Диалоговое окно SQL Server Enterprise Manager

Для создания базы данных с помощью SQL Server Enterprise Manager выполняют следующее.

1. Запуск названной программы, выбор в диалоговом окне сервера баз данных и выделение папки Databases (рис. 13.1). На правой панели программы отображается окно базы данных.

2. Выполнение команды Action [ New Databases (Действие [ Создать базу данных).

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

Для создания базы данных предназначена команда Transact-SQL следующего формата:

CREATE DATABASE <имя БД>

[ON [PRIMARY] <спецификация файла>[,...n]]

[LOG ON спецификация файла> [,.. .n]]

[FOR RESTORE]

<спецификация файла>:: =

(NAME = логическое имя файла,

FILENAME ='имя файла операционной системы'

[, SIZE = размер]

[, MAXSIZE= { максимальный размер | UNLIMITED}]

[, FILEGROWTH = приращение ])

Параметры команды CREATE DATABASE имеют следующий смысл:

 ON определяет список файлов на диске для размещения информации базы данных;

 PRIMARY определяет первичный файл. Если параметр опущен, то первичным является первый файл в списке;

 n - на месте этого символа можно указать дополнительные файлы для размещения БД;

 LOG ON определяет список файлов на диске для размещения журнала транзакций;

 SIZE определяет первоначальный размер файла, минимальное значение параметра 512 кбайтов, если он не указан, по умолчанию принимается 1 Мбайт;

 MAXSIZE определяет максимальный размер файла базы данных. При значении параметра UNLIMITED максимальный размер базы данных ограничивается свободным местом на диске;

 FOR RESTORE не разрешает пользователю доступ к базе данных до завершения операции RESTORE.

Перед созданием базы данных нужно открыть и установить текущей базу данных master с помощью команды USE master.

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

CREATE DATABASE owndbase266

ON PRIMARY

(NAME = owndbase266_data,

FILENAME ='C:\mssq117\data\owndbase266_data.mdf',

SIZE =4 MB,

FILEGROWTH = 1 MB)

[LOG ON

(NAME =owndbase266_log,

FILENAME ='C:\mssq117\data\owndbase266_log.ldf,

SIZE =2 MB,

FILEGROWTH = 1 MB)

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

18.5. Работа с таблицами

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

Создание таблиц в SQL Server можно выполнить следующими способами:

 с помощью SQL Server Enterprise Manager;

 с помощью Visual Database Tools;

 на языке Transact-SQL с помощью команды CREATE TABLE.

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

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

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

При создании таблицы для каждого ее поля задается тип Банных, определяющий тип информации, которую можно хранить в поле. После определения типа данных столбца он сохраняется как постоянная характеристика. В отличие от предыдущих версий, в SQL Server 7.0 можно изменять структуру таблицы, например типы данных полей, с помощью оператора ALTER TABLE. Типы данных (системные) таблиц SQL Server приведены в табл. 18.1.

Таблица 18.1. Типы данных

Тип

Размер, байтов

Описание

Binary(n)

до 1,2Гб

Двоичные данные для хранения файлов

Varbinary(n)

до 1,2Гб

Двоичные данные для хранения файлов

Char(n)

n

Символьная строка

Vchar(n)

n

Символьная строка

Datetime

8

Дата и время

Smalldatetime

4

Дата и время

Decimal(p,s) 

1-17 

Число с общим количеством цифр р и числом s цифр после запятой 

Numeric(p,s)

 

1-17

 

Число с общим количеством цифр р и числом s цифр после запятой 

Float

8

Число с плавающей точкой

Real

4

Число с плавающей точкой

Int

4

Целое число

Sntallint

2

Целое число

Tinyint

1

Целое положительное число от 0 до 255

Money

8

Денежное значение

Smallmoney

4

Денежное значение

Bit

1

Булево значение

Timestamp

То же, что и binary(8)

Text

4

Поле ссылки на объект OLE

Image

4

Поле примечаний

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

Для создания постоянной таблицы с помощью SQL Server Enterprise Manager нужно выполнить следующее:

 запустить SQL Server Enterprise Manager;

 в открывшемся диалоговом окне выбрать серверную группу щелчком по знаку “+” слева от имени группы;

 выбрать базу данных (рис. 18.2), в которой предполагается создание таблицы;

  задать команду Table (Таблица) меню Manage (Управление);

 в открывшемся одноименном диалоговом окне указать имена столбцов таблицы, определив для каждого столбца тип данных, при необходимости размер, допустимость значения Null и значение по умолчанию (Default);

 сохранить таблицу (кнопка Save Table), указав ее имя в окне Specify Table Name.

Рис. 18.2. Вид окна с объектами базы данных

Создание таблицы можно выполнить по команде Transact-SQL следующего формата:

CREATE TABLE [[база данных.] владелец. ] таблица

(поле тип_данных [notnul [ nul]

[IDENTITY] [(начальное_значение, приращение)][ограничение]

[, поле тип_данных [not nul [ nul]

[IDENTITY] [(начальное_9начение, приращение)]]

[ограничения]...)

[ON группа_файлов]

Имя таблицы и столбца таблицы может иметь до 128 символов. Имена столбцов заключают в круглые скобки. Указание свойства IDENTITY при описании позволяет задать поле с автоматическим приращением значений.

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

При создании таблицы можно указать базу данных и владельца, которым она принадлежит. Удобно предварительно определить текущую базу данных с помощью команды USE база_данных. При этом все последующие команды выполняются для этой базы данных.

Часто таблицы создают с помощью учетной записи сетевого администратора для ограничения прав доступа к ним. Владелец базы данных и сетевой администратор могут выдавать разрешение другим пользователям на выполнение команды CREATE TABLE для создания таблиц в базе данных.

Пример 1. Создание таблицы.

Рассмотрим оператор создания таблицы с тремя столбцами целочисленных типов.

CREATE TABLE table-1

(int-1 TINYINT, int2 INT, int2 SMALLINT)

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

Свойство IDENTITY можно задать для столбцов численных типов: INT, SMALLINT, TINYINT, DECIMAL(p, 0) и NUMERIC(p, 0). Это свойство нельзя задавать для столбцов с атрибутом NULL. Если при задании свойства IDENTITY не указать начальное значение и приращение, то по умолчанию они примут значение 1.

Основные действия по работе с данными таблиц заключаются в выборке, добавлении, удалении и изменении информации.

Добавление данных в таблицу можно выполнить с помощью оператора INSERT следующего формата

INSERT INTO имя_таблицы

(столбец_1 ,...,столбец_n) VALUES (’cтpoкa_1’,...,'cтpoкa_n')

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

Каждый оператор INSERT позволяет добавить в таблицу одну запись (строку).

Пример 2. Добавление записи в таблицу.

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

INSERT INTO table-!

(int1, int2, int2)

VALUES(255,32767,50000)

Пример 3. Добавление записи в таблицу.

INSERT INTO table2

(Name, Sity, Year)

VALUES ('Salivan', ‘New York’, 2000)

Атрибуты NULL и NOT NULL. Задание для столбца таблицы атрибута NULL позволяет опустить при вводе данных значения этого столбца. В случае задания атрибута NOT NULL SQL Server не позволяет оставить этот столбец пустым при вставке строки в таблицу. По умолчанию столбцу назначается атрибут NOT NULL.

Определенное для столбца значение NULL отличается от пробела, нуля и ASCII-символа нуля. Значение NULL для столбца интерпретируется как неопределенное или незаданное, поскольку при вставке строки в таблицу соответствующее значение не вводилось. При ссылке на строку в месте значения столбца, содержащего NULL, отображается элемент NULL, указывающий на то, что в этом месте строки значение не вводилось.

Если при добавлении записи в таблицу пропустить одно или несколько имен столбцов, то в них помещается стандартное значение или NULL. Правила неявного присвоения значения столбцу, если в операторе INSERT значение столбца не указано, приведены в табл. 18.2.

Для вставки значения NULL в столбец, для которого задан атрибут NULL, следует в списке за ключевым словом VALUES для этого столбца указать NULL.

Таблица 18.2. Правила неявного присваивания значения столбцу

Атрибут столбца

Стандартное значение

Результат

NULL

He определено

NULL

NOT NULL

He определено

Ошибка, строка не введена

NULL

Определено

Стандартное значение

NOT NULL

Определено

Стандартное значение

Для выборки данных из таблиц используется оператор SELECT языка SQL. С его помощью можно создавать разнообразные запросы на отбор информации из таблиц, позволяя указывать столбцы и строки. Для подготовки SQL-запроса на выборку данных из таблицы можно использовать следующие средства:

·        утилиту Query Analyzer;

·        утилиту командной строки ISQL;

·        утилиту командной строки OSQL.

Отличие OSQL от ISQL заключается в том, что в OSQL для подключения к базе данных используется механизм ODBC, а не механизм DB-LIB. Обе утилиты обладают одинаковым синтаксисом, но вызываются своими именами.

Наиболее удобно использование программы Query Analyzer, обладающей графическим интерфейсом пользователя (GUI - Graphic User Interface) и обеспечивающей удобную корректировку запросов. Кроме того, этой программой можно пользоваться непосредственно из программы SQL Server Enterprise Manager, которая облегчает работу с табличными структурами, другими серверами и другими возможностями SQL Server.

Для запуска и использования утилиты Query Analyzer из программы SQL Server Enterprise Manager выполняются следующие действия.

1. Выбор в окне программы SQL Server Enterprise Manager сервера баз данных из списка зарегистрированных серверов.

2. Открытие окна Анализатора запросов с помощью команды Tools | SQL Server Query Analyzer (Сервис | Анализатор запросов SQL Server).

3. Выбор в диалоговом окне из списка DB базы данных, если она не выбрана ранее.

4. Ввод оператора запроса в верхней половине окна программы, нажатие клавиши <F5> и ввод команды Query ( Execute (Запрос | Выполнить) или для отображения результатов запроса с линиями сетки - ввод команды Query | Results In Grid, нажатие клавиши <F5>.

Результаты запроса автоматически отображаются в нижней части окна программы Query Analyzer по мере получения информации.

18.6. Индексы и ключи

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

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

При создании индекса SQL Server выполняет сканирование таблицы, выбирает значения в индексируемом столбце и записывает на индексную страницу указатели на страницы данных и идентификаторы строк для индексируемых значений.

В SQL Server допускаются следующие два типа индексов: кластерные и некластерные.

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

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

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

При выборе столбца для создания индекса нужно учитывать следующее:

- нельзя создавать индекс для столбцов, имеющих тип данных BIT, TEXT или IMAGE;

-  нельзя создавать индекс для столбцов, имеющих тип данных CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY и VARBINARY;

- в качестве основного кандидата для создания индекса целесообразно использовать первичный ключ;

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

- целесообразно использовать в индексе столбцы, содержащиеся в директивах ORDER BY (упорядочение по) и GROUP BY (сортировка по).

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

В SQL Server индексы можно создать двумя способами: с помощью программы SQL Server Enterprise Manager и с помощью оператора CREATE INDEX языка Transact SQL. Создать индекс для таблицы может только ее владелец.

Для создания индекса с помощью программы SQL Server Enterprise Manager выполняется следующее:

1. Запуск названной программы из группы Microsoft SQL Server 7.0.

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

3. Выполнение команды меню Action | All tasks | Manage Indexes (Действие | Все задачи | Работа с индексами).

4. В открывшемся диалоговом окне Manage Indexes (Работа с индексами) нажатие кнопки New (Создать).

5. В очередном диалоговом окне Create New Index (Создание нового индекса) в поле Index Name (Имя индекса) задание имени индекса.

6. В группе Index Options (Параметры индекса) задание параметров индекса с помощью флажков и нажатие ОК.

Выбор кластерного индекса определяется в диалоговом окне Create New Index (Создание нового индекса) в группе Index Options (Параметры индекса) установкой одноименного флажка Clustered Index (Кластерный индекс).

Можно указать явно для SQL Server какой индекс следует использовать путем задания его в директиве FROM. С этой целью в операторе SELECT используется директива INDEX. Упрощенно формат оператора выборки данных можно представить следующим образом:

SELECT...

FROM имя_таблицы (INDEX ==n)

Здесь ключевое слово INDEX предписывает, чтобы SQL Server использовал индекс, определяемый значением п. Если значение n равно нулю, то выполняется сканирование таблицы. При единичном значении n используется кластерный индекс, если он имеется в таблице. Остальные значения n определяют порядковые номера индексов, которые следует использовать.

В SQL Server имеется два способа отображения информации об индексах: с помощью диспетчера индексов Index Manager программы SQL Server Enterprise Manager, а также с помощью системной хранимой процедуры sp_helpindex или с помощью хранимой процедуры ODBC sp_statistics.

Индексы можно удалять двумя способами: с помощью программы SQL Server Enterprise Manager и с помощью оператора DROP INDEX языка Transact SQL. Для удаления индекса с помощью программы SQL Server Enterprise Manager выполняется следующее:

1. Запуск программы из группы Microsoft SQL Server 7.0.

2. Выбор сервера, базы данных и таблицы.

3. Выполнение команды меню Action | All tasks | Manage Indexes (Действие | Все задачи | Работа с индексами).

4. В открывшемся диалоговом окне Manage Indexes (Работа с индексами) выбор удаляемого индекса и нажатие кнопки Delete (Удалить).

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

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

В SQL Server первичные и внешние ключи можно создать следующими тремя способами: с помощью программы SQL Server Enterprise Manager, с помощью оператора ALTER TABLE...ADD CONSTRAINT языка Transact SQL и с помощью параметра PRIMARY/FOREIGN KEY оператора CREATE TABLE.

Для добавления первичного ключа с помощью программы SQL Server Enterprise Manager выполняется следующее:

1. Запуск программы из группы Microsoft SQL Server 7.0.

2. Выбор сервера, базы данных и таблицы.

3. Выполнение команды меню Action | Design Table (Действие | Конструктор таблиц).

4. Выделение столбцов, определяемых в качестве первичных индексов, и выполнение команды контекстного меню таблицы Set Primary Key (Установить первичный ключ).

5. Сохранение изменений в таблице с помощью щелчка на кнопке Save (Сохранить) панели инструментов (с изображением дискеты).

В SQL Server имеется два способа отображения информации о ключах: с помощью диспетчера таблиц Table Manager программы SQL Server Enterprise Manager и системных хранимых процедур sp_help и sp_helpconstraint, а также с помощью хранимых процедур ODBC sp_keys и sp_fkeys.

Первичные и внешние ключи можно удалить с помощью программы SQL Server Enterprise Manager и с помощью оператора ALTER TABLE-DROP CONSTRAINT языка Transact SQL.

18.7. Хранимые  процедуры и триггеры

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

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

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

По области видимости различают хранимые процедуры следующих четырех типов: системные (System), локальные (Local), временные (Temporary) и удаленные (Remote).

Системные хранимые процедуры размещаются в базе данных Master, используются сервером и администратором. Имена системных процедур начинаются с символов sp_. Для создания собственной системной хранимой процедуры достаточно присвоить ей имя, начинающееся с sp_ и поместить ее в базу данных Master.

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

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

Удаленные хранимые процедуры можно вызывать с сервера, отличного от текущего. При этом текущий сервер играет роль промежуточного звена. Кроме того, удаленную хранимую процедуру можно вызвать непосредственно с сервера, на котором она находится.

Еще одним типом хранимых процедур являются так называемые расширенные хранимые процедуры (extended stored procedures). Они пишутся на языке программирования, таком как С. Расширенные хранимые процедуры оформляются как функции в составе библиотек динамических связей - DLL (Dinamic Link Library), что повышает скорость их выполнения. Имена расширенных хранимых процедур начинаются с символов хр_.

Хранимые процедуры можно создавать как последовательности операторов Transact-SQL (см. одноименный подраздел) с использованием оператора CREATE PROCEDURE и с помощью программы SQL Server Eterprise Manager.

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

тип[=default][OUTput]

тип [=default][OUTput]])]

, ENCRYPTION]

AS

торы_Transact-SQL>

Использование в имени процедуры составляющей ";версия" (задаваемой целыми числами) позволяет получить группу одноименных процедур с одинаковыми именами и разными версиями, например: ргос;1, ргос;2, ргос;3 и т.д. Это удобно тем, что всю такую группу процедур можно удалить одной следующей командой:

DROP PROCEDURE ргос

Вызов хранимой процедуры для выполнения осуществляется по ее имени. Если хранимая процедура является частью группы, то вызов ее осуществляют с помощью команды ЕХЕС. Например,

Пример 1. Создание и использование хранимой процедуры. Создадим процедуру, выполняющую отбор записей из таблицы Employee, в которой условия отбора определяются с помощью двух параметров.

CREATE PROCEDURE procSelect

(@р1 char(30), @p2 char (20))

AS

SELECT Name, Department, Cost FROM Employee

WHERE @p1=@p2

Обращение к процедуре может иметь следующий вид:

procSelect Name, 'Kooper'

Возможным результатом такого обращения будет строка вида:

Name  Department  Cost

-----------------------------------------------------

Kooper  Trade   3000

Создание хранимой процедуры с помощью программы SQL Server Eterprise Manager включает следующие действия.

1. Запуск этой программы из группы программ SQL Server 7.0.

2. Выбор в открывшемся диалоговом окне программы сервера баз данных и базы данных.

3. Выбор элемента Stored Procedures (Хранимые процедуры) и выполнение команды его контекстного меню New Stored Procedure (Создать хранимую процедуру).

4. В открывшемся диалоговом окне Stored Procedure Properties (Свойства хранимой процедуры) в поле Text (Текст) ввод операторов Transact-SQL создаваемой процедуры и указание имени процедуры на месте фразы < PROCEDURE NAME> (рис. 18.3).

5. Нажатие кнопки Check Syntax (Проверка синтаксиса) для проверки отсутствия синтаксических ошибок и при необходимости корректировка операторов для устранения ошибок.

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

Рис. 18.3. Диалог создания хранимой процедуры

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

Триггер и вызвавший его оператор Transact-SQL рассматриваются как единая транзакция, отменяемая (откатываемая) из триггера. Обычно в состав триггера входит набор команд выполнения некоторых действий над данными или проверки определенных условий. При невозможности обработки данных или невыполнимости условий происходит откат транзакции.

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

Для создания триггеров используется оператор CREATE следующего формата:

СРЕАТЕ TRIGGER [владелец.]имя_триггера

ON [владелец.] имя_ таблицы

FOR {INSERT, UPDATE, DELETE}

[WITH ENCRYPTION]

AS

<oпepaторы_SQL>

Здесь ключевые слова INSERT (Вставить), UPDATE (Обновить) и DELETE (Удалить) определяют операции, которые инициируют выполнение триггера. Параметр WITH ENCRYPTION (с шифрованием) служит для предотвращения возможности прочтения текста триггера после помещения его на сервер. SQL Server сохраняет текст триггера в таблице системного каталога syscomments.

Пример 2. Создание триггера вставки.

Рассмотрим создание триггера, который выполняется при вставке записи в таблицу schet. В таблице schet хранится информация о покупках и есть поле, где указывается номер накладной о покупке одной или нескольких единиц товара. Общая сумма стоимости по накладной хранится в отдельной таблице prod. Назначением создаваемого нами триггера является срабатывание на добавление записи в таблицу schet и выполнение добавления записи в таблицу prod, причем номер накладной в обеих таблицах должен совпадать.

CREATE TRIGGER schetJnsert

ON schet

FOR INSERT

AS

INSERT INTO prod (schet, sum)

VALUES (vstavka. schet, vstavka.sum)

Замечание.

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

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

18.8. Обеспечение безопасности

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

В SQL Server на уровне сервера используются следующие средства обеспечения безопасности:

идентификация (identification) по имени пользователя при входе в систему;

 аутентификация (authentication) подлинности пользователя с помощью пароля;

 учетная запись (login);

 встроенные роли сервера (fixed server roles).

На сервере система защиты SQL Server, может быть реализована в двух следующих режимах:

 стандартном - комбинацией средств защиты SQL Server и Windows NT;

  интегрированном - использованием только средств защиты Windows NT.

Названные режимы определяют, как пользователи SQL Server регистрируются на сервере и как они входят в операционную систему Windows NT.

Настройка сервера баз данных на определенный режим функционирования системы защиты выполняется с помощью программы SQL Server Enterprise Manager следующим образом:

1. Запуск программы SQL Server Enterprise Manager.

2. Выбор нужного сервера баз данных, открытие окна его свойств, например, с помощью команды Properties (Свойства) контекстного меню и выбор вкладки Security.

3. С помощью переключателей группы Authentication: (аутентификация:) установка требуемого способа защиты: стандартный - SQL Server and Windows NT, интегрированный - Windows NT only.

4. С помощью переключателей группы Audit level: (Уровень аудита:) выбирается требуемый вариант учета попыток регистрации пользователей:

  None - попытки доступа не протоколируются;

 Success - протоколируются только успешные регистрации;

 Failure - протоколируются неуспешные попытки регистрации;

 All - протоколируются все регистрации.

В зависимости от конфигурации сервера информация системы аудита содержится в журнале приложений операционной системы (Windows NT application log) или в журнале ошибок SQL Server 7.0 или в обоих журналах. Просмотреть содержимое журналов можно с помощью утилит Windows NT и Enterprise Manager соответственно.

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

В интегрированном режиме защиты контроль над устанавливаемыми пользователями соединениями осуществляет операционная система Windows NT. При этом используются списки контроля доступа ACL (Access Control List). Достоинствами интегрированного режима защиты являются обеспечение доступа ко всем ресурсам домена Windows NT при задании пароля и использование шифрования при передаче по сети.

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

 как зарегистрированный пользователь при опознании имени;

 как стандартный пользователь с именем Guest;

 как системный администратор Windows NT-SA (System Administrator).

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

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

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

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

В записи пользователя может быть определена роль пользователя - одна или несколько. Напомним, что понятие роли введено в SQL Server 7.0 вместо понятия группы. С ролью пользователя связывают его функции защиты, например, пользователю (одному или нескольким) может быть назначена роль администратора системы безопасности, что дает ему право управлять всеми учетными записями в системе.

Создание учетной записи может быть выполнено с помощью программы SQL Server Enterprise Manager следующими действиями:

1. Запуск программы.

2. Открытие в окне программы требуемого сервера, затем его папки Security (Безопасность) и выделение объекта Logins (Учетные записи).

3. Выполнение команды Action ) New Login (Действие | Новая учетная запись).

4. В открывшемся диалоговом окне ввод данных о новой учетной записи пользователя (на вкладке General (Общие) окна указание имени учетной записи и пароля).

5. На вкладке Database Access (Доступ к базе данных) указание баз данных, к которым пользователю разрешается доступ, нажатие ОК.

Создание записи пользователя в базе данных можно выполнить с помощью программы SQL Server Enterprise Manager следующими действиями:

1. Запуск программы и выделение в окне программы требуемой базы данных.

2. Выполнение команды Action | New | Database User (Действия | Создать | Пользователь базы данных). В результате откроется диалоговое окно Database User Properties (Свойства пользователя базы данных).

3. Выбор в списке Login name: (Учетная запись:) учетной записи, для которой в базе данных создается запись пользователя.

4. В поле User name: (Имя пользователя:) указание имени пользователя.

5. Выбор необходимых для данного пользователя ролей в списке Database role membership: (Роли базы данных:).

В базе данных роли могут иметь один из двух типов: standard и application. Роль типа standard назначается пользователю и обеспечивает ему определенные права доступа к объектам базы данных.

Роль типа application назначается приложениям для предоставления прав доступа к объектам базы данных. Роли типа application требуют указания пароля для получения доступа.

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

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

Наибольшими правами доступа обладают следующие категории пользователей:

 системный администратор (SA) имеет все права доступа ко всем объектам во всех базах данных сервера;

 владелец базы данных (Database Owner) имеет все права доступа ко всем объектам его базы данных.

Право доступа к объекту (object permission) представляет собой разрешение на выполнение конкретных действий над объектами базы данных, например, таблицами, представлениями и хранимыми процедурами. В частности, для таблиц существуют следующие типы прав доступа: SELECT - разрешение выборки данных; INSERT - разрешение вставлять данные; UPDATE - разрешение изменять данные и т.д.

Задание и отмену прав доступа к объектам базы данных можно выполнить с помощью команды GRANT и REVOKE языка Transact SQL и с помощью программы SQL Server Enterprise Manager.

Изменение прав доступа к объектам базы данных с помощью программы SQL Server Enterprise Manager включает следующие действия:

1. Запуск программы и выделение в окне программы требуемой базы данных, для которой выполняется изменение прав доступа к объектам.

2. Выполнение команды Action | Properties (Действия | Свойства). В результате откроется окно свойств базы данных.

3. На вкладке Permission (Разрешения) выбор требуемого пользователя или роли и установление требуемых прав доступа к объектам с помощью флажков, нажатие ОК.

18.9. Организация взаимодействия клиент - сервер

Напомним, что при использовании технологии клиент-сервер приложение разделяется на две (или более) части. Клиентская часть (Front-end) обеспечивает удобный графический интерфейс и размещается на компьютере пользователя. Серверная часть (Back-end) осуществляет управление данными, разделение информации, администрирование и обеспечивает безопасность информации. Клиентское приложение формирует запросы к серверу базы данных, на котором выполняются соответствующие команды. Результаты выполнения запроса пересылаются клиенту.

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

 перенос персональной базы данных на сервер для последующего ее коллективного использования как корпоративной базы данных;

 организация запросов к корпоративной базе данных, размещенной на сервере, со стороны компьютера-клиента;

 разработка клиентского приложения для удаленного доступа к корпоративной базе данных со стороны компьютера-клиента;

 администрирование сервера со стороны клиента. Рассмотрим названные задачи более подробно.

Перенос персональной базы данных на сервер

Задача переноса персональной базы данных на сервер может возникать в ситуациях, когда требуется обеспечить коллективный доступ к базе данных, разработанной с помощью персональной СУБД, такой как Microsoft Acces или Microsoft Visual FoxPro. Для решения этой задачи в составе названных персональных СУБД имеются соответствующие средства. Так, в Microsoft Acces 97 имеется Upsizing Wizard (Мастер "наращивания"), предназначенный для преобразования базы данных Access к формату SQL Server. В составе пакета Microsoft Visual FoxPro 5.0 имеется аналогичный Мастер, предназначенный для преобразования базы данных Visual FoxPro к формату SQL Server или Oracle.

Способы взаимодействия при подготовке запросов к базе данных

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

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

 интерфейса DB-Library или DB-LIB (библиотек баз данных);

 технологии ODBC (совместимости открытых баз данных);

 интерфейса OLE DB (связывания и встраивания объектов баз данных);

 технологии DAO (Data Acces Object - объектов доступа к данным);

 технологии ADO (ActiveX Data Object - объектов данных ActiveX).

Названные интерфейсы и технологии могут применяться совместно, например, ODBC и DAO или ODBC, ADO и OLE DB. Охарактеризуем подробнее названные подходы к организации взаимодействия клиентской и серверной частей баз данных.

Интерфейс DB-Library представляет собой специализированную библиотеку функций API, осуществляющих прямой доступ к SQL Server из среды систем программирования С и Visual Basic. Они предоставляют средства отправки запросов и получения информации от SQL Server.

Работа с библиотекой DB-LIB предполагает следующую стандартную последовательность действий: регистрацию в системе (установление соединения), выполнение нескольких действия на сервере и отключение сервера (закрытие соединения).

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

DB-LIB представляет интерфейс SQL Server уровня прикладного программирования. Отметим, что этот уровень не используется непосредственно в среде персональных СУБД, таких как Access и Visual FoxPro. В них используются промежуточные абстрактные уровни ODBC или OLE DB, упрощающие доступ к базам данных.

Технология DB-LIB использована в большом числе разработанных ранее информационных систем. В настоящее время эта технология не поддерживается фирмой Microsoft и вместо нее рекомендуется использовать средства OLE DB.

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

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

Достоинством технологии ODBC является простота разработки приложений, обусловленная высоким уровнем абстрактности интерфейса доступа к данным практически любых существующих типов СУБД (см. подр. 4.4). При этом возможно создание источника данных, связанного с любым типом базы данных. Используя эту технологию, можно создавать клиент-серверные приложения, причем средствами персональных СУБД целесообразно разрабатывать клиентскую часть приложения, а средствами SQL Server - серверную часть.

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

OLEDB представляет собой интерфейс прикладного программирования API, который позволяет приложениям СОМ (Component Object Model - объектная модель компонентов) потреблять данные из источника данных OLE DB. Источник данных OLE DB включает данные, хранимые в различных форматах, не только в формате баз данных SQL. Приложение использует провайдер OLE DB для доступа к источнику данных OLE DB. Провайдер OLE DB представляет собой компонент СОМ, позволяющий принимать вызовы OLE DB API и выполнять все необходимое для обработки запроса к источнику данных.

В ранних версиях SQL Server приложения OLE DB должны были использовать провайдер OLE DB для ODBC, основанном на драйвере Microsoft SQL Server ODBC. Поскольку приложения по-прежнему могут использовать провайдер OLE DB для ODBC, поэтому более эффективно использование только провайдера OLE DB для SQL Server.

Интерфейс прикладного программирования OLE DB рекомендуется использовать для создания средств и утилит, или разработок системного уровня, нуждающихся в высокой производительности или доступе к свойствам SQL Server, недоступным с помощью технологии ADO. Основные возможности спецификации OLE DB обеспечивают полную функциональность доступа к данным, требуемую большинством приложений. Кроме того, OLE DB допускает индивидуальным провайдерам определять специфические механизмы поддержки дополнительных свойств процессора данных, доступного провайдеру. Приложения ADO могут не иметь доступа к некоторым свойствам SQL Server, проявляемым через специфические свойства провайдера OLE DB для SQL Server. Поэтому приложения, желающие воспользоваться такими специфическими свойствами, должны использовать OLE DB API.

В SQL Server 7.0 процессор баз данных сервера использует OLE DB для связи: между внутренними компонентами, такими как процессор хранения и процессор отношений; между установками SQL Server при использовании удаленных хранимых процедур; как интерфейс к другим источникам данных для распределенных запросов.

Технология DAO означает использование объектов, методов и свойств, которые существенно упрощают работу приложения с базой данных. Для обмена информацией с SQL Server применяются уровни доступа процессора баз данных Jet SQL Server и ODBC. Кроме того, при этом создается еще один уровень абстракции между приложением и функциями ODBC, используемыми при выполнении запросов.

При использовании технологии DAO работа с базами данных, таблицами, представлениями и т. д. ведется с использованием коллекций объектов. При этом обеспечиваются большие удобства в работе с объектами баз данных. К примеру, для создания представления проще вызвать метод Add соответствующего представлению объекта, чем применять стандартные средства ODBC с указанием имен используемых для этих целей хранимых процедур.

При работе с базами данных технология DAO применима для большинства источников данных, поддерживаемых средствами ODBC. Его можно применять также на других платформах и в системах программирования. В частности, технология DAO поддерживается версиями Visual Basic.

В 'настоящее время технология DAO постепенно вытесняется технологией ADO, которая позволяет разрабатывать приложения Web для работы с базами данных.

Технология ADO основана на объектной модели, в которой объекты имеют наборы коллекций, методов и свойств, обеспечивающие поддержку баз данных. Объекты этой технологии предоставляют наиболее широкие возможности по интеграции приложений с базами данных. С использованием технологии ADO приложения для работы с базами данных можно создавать в различных системах программирования, например, Visual Basic и Visual Basic for Application. Кроме того, объекты можно использовать при разработке Web-приложений для среды ASP (Active Server Pages -активных серверных страниц), или приложений ASP.

Объекты ADO доступны в среде ASP и функционируют на уровне OLE DB. При этом технология ADO обеспечивает установление соединений ODBC и работу с уровнем OLE DB. При организации доступа к базе данных из приложения ASP инициатором установки соединения с сервером базы данных является клиентское приложение. Причем, приложения ASP выполняются в среде Internet Information Server, а не на SQL Server.

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

Администрирование сервера со стороны клиента

Для администрирования сервера со стороны клиента могут использоваться компоненты клиентской части SQL Server 7.0, например, SQL Server Enterprise Manager. Кроме того, для этих целей может использоваться так называемая технология SQL-DMO.

Технология SQL-DMO (SQL Distributed Management Objects - распределенного управления объектами SQL) позволяет приложениям, написанным на языках программирования, поддерживающих OLE или СОМ, выполнять администрирование всеми компонентами SQL Server. SQL-DMO представляет собой интерфейс прикладного программирования API, используемый SQL Server Enterprise Manager. Поэтому приложения, используя SQL-DMO, могут выполнять все функции, выполняемые программой SQL Server Enterprise Manager.

При использовании технологии SQL-DMO работа ведется с объектами, коллекциями, свойствами и методами объекта SQL-DMO. Кроме того, возможно выполнение и посылка на сервер команд SQL Server с помощью вызова методов Executelmmediate и ExecuteWithResults.

18.10. Обработка данных с помощью ODBC

Рассмотрим организацию обработки в базе данных на сервере SQL Server с помощью технологии ODBC и использовании Access в качестве клиентского приложения. При использовании в качестве клиентского приложения других персенальных СУБД, например, Visual FoxPro общая технология организации обработки данных сохраняется.

При использовании в клиентском приложении средств ODBC осуществляется обращение к определенному источнику данных, а через него - к СУБД, которую он представляет. При установке средств ODBC устанавливается общая подсистема ODBC и определяются пары “драйвер - база данных”, которым задаются имена, используемые при установке соединения с базой данных. Соответствующие пары называются DNS (Data Sourse Names - имена источников данных, или поименованные источники данных).

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

Создание и настройка источника данных

Создание источника данных выполняется с помощью утилиты ODBC Data Source Administrator, вызываемой из окна панели управления (Control Panel). С ее помощью (рис. 18.4) можно создать новое соединение и настроить параметры имеющихся соединений.

Для создания нового соединения нужно в окне утилиты ODBC Data Source Administrator (рис. 18.4) нажатием кнопки Add.. (Добавить) вызвать Wizard (Мастер), создающий источник данных ODBC, и в его диалоговых окнах указать параметры. В их число входят: имя и описание источника данных; сервер, с которым устанавливается соединение; метод аутентификации; имя базы данных; имя базы данных по умолчанию и некоторые другие. Желательно указать имя используемой базы данных. Это гарантирует то, что при установке соединения будет выбрана именно эта база данных.

Рис. 18.4. Окно настройки параметров соединения

Для изменения параметров имеющегося соединения достаточно в окне утилиты ODBC Data Source Administrator (рис. 18.4) выбрать нужное соединение, нажатием кнопки Configure... (Настройка...) вызвать диалог настройки параметров соединения и внести нужные изменения.

Access 97 поддерживает следующие три типа источников данных ODBC:

 пользовательские (обычные), специфичные для каждого пользователя компьютера-клиента, - можно создавать с помощью утилиты ODBC Administrator и с помощью Access;

 системные, - также можно создавать с помощью утилиты ODBC Administrator и с помощью Access;

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

Использование баз данных

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

Подключение таблиц базы данных SQL Server, например dbs, включает следующую последовательность действий:

1. Запуск Access и создание базы данных с произвольным именем, например, dba.mdb.

2. С помощью команды меню Файл | Внешние данные | Связь (File | Get External Data | Link Tables) открытие диалогового окна Связь (Link).

3. В списке Тип файла (Files of Type) выбор элемента Базы данных ODBC (ODBC Databases).

4. В очередном диалоговом окне Select Data Source (Выбор источника данных) на вкладке Machine Data Source (Машинный источник данных) выбор двойным щелчком источника данных в списке.

5. В очередном диалоговом окне SQL Server Login (Регистрация SQL Server) указание имени и пароля, нажатие ОК для регистрации в SQL Server.

После подключения к базе данных dbs с помощью ODBC API открывается диалоговое окно Link Tables (Связь с таблицами), в котором приводится список имен таблиц с префиксами dbo (database owner - владелец базы данных).

6. Подключение при необходимости всех таблиц нажатием кнопки Select All (Выделить все), затем ОК.

7. Для обновления таблиц Access нужно иметь уникальный индекс. Если в таблице SQL Server нет индекса первичного ключа, то при попытке обновления таблицы открывается диалоговое окно Select Uniqe Record Identifier (Выбор уникального индекса). В нем задаются поля первичного ключа и нажимается ОК.

Подключенные таблицы выводятся в списке таблиц в окне базы данных. Значок глобуса слева от имени таблицы указывает на подключение с помощью ODBC. В именах таблиц вместо точек используется символ подчеркивания.

Создание запроса к базе данных SQL Server включает следующие шаги:

1. Создание нового запроса и добавление в него требуемых таблиц из базы данных SQL Server, например, dbs.mdb.

2. Перетаскивание требуемых полей из добавленных таблиц в строку Field (Поле) бланка запроса.

3. Нажатие на панели инструментов кнопки Run (Запуск) для вывода результатов выполнения запроса.

4. Закрытие и сохранение запроса.

Приведенная выше процедура подключения таблиц используется в большинстве случаев для баз данных SQL Server при наличии драйверов ODBC.

В SQL Server можно писать хранимые процедуры и выполнять их вместо посылки серверу отдельных команд. Выполнение запроса в виде хранимой процедуры осуществляется быстрее, чем с помощью ядра базы данных Jet. Это обусловлено уменьшением объема передаваемых данных и тем, что хранимые процедуры выполняются быстрее, поскольку предварительно компилируются. Кроме того, запрос к SQL Server применяется при необходимости использовать средства Transact-SQL, отсутствующие в SQL Access.

Для преобразования запроса Access в запрос к SQL Server выполняется следующее:

1. Открытие ранее созданного запроса в режиме Конструктора запросов.

2. Выполнение команды Query [ SQL-Specific | Pass-Through (Запрос | Запрос к серверу | К серверу). В результате открывается диалоговое окно, содержащее команду SQL.

3. В тексте команды запроса SQL из каждого имени таблицы удаление префикса dbo_.

4. Сохранение запроса к серверу с помощью команды меню File | Save (Файл | Сохранить).

5. Нажатие на панели инструментов кнопки Run (Запуск) для выполнения запроса к серверу. В открывшемся диалоговом окне Select Data Source (Выбор источника данных) выбор нужного источника данных, нажатие ОК.

6. Задание в очередном окне имени и пароля, нажатие ОК. Результирующее множество запроса будет таким же, как и при выполнении стандартного запроса ядром SQL Server.




Возможно эти работы будут Вам интересны.

1. Текстовый редактор Microsoft Word

2. Microsoft Ajax. Проект Розсилка повідомлень

3. Microsoft Office Programmability in C# and Visual Basic

4. Применение программ пакета Microsoft Office

5. Базы данных РВ. Структура. Применение. Особенности. Особенности Industrial SQL Server. Функциональные возможности сервера базы данных. Интеграция с другими компонентами комплекса

6. Використання Visual Basic for Application у Microsoft Excel

7. Обработка экспериментальных данных в металлургии с использованием Microsoft Excel

8. Microsoft Access - система управления реляционными базами данных

9. Решение задачи нелинейного программирования с использованием Microsoft Excel

10. Використання надбудови Пошук розв’язку електронних таблиць Microsoft Excel