Использование средств Database Tools интегрированного комплекта средств разработки Visual Studio 6.0 для работы с базами данных Microsoft SQL Server 7.0
Нения Инграм
Microsoft Corporation
Ноябрь 1998
Краткий обзор: Работа с данными из различных СУБД в Microsoft SQL Server 7.0 и Visual Studio 6.0 Database Tools. (6 печатных страниц)
С помощью подключенного сервера (linked server) и предложения TOP в Query Designer вы можете:
· Организовать доступ к внешним источникам данных OLE DB, используя подключенный сервер
· Ограничить объем возвращаемых данных, используя предложение TOP.
· Увеличить производительность и эффективность базы данных, используя группы файлов (filegroups).
· Генерировать уникальные идентификаторы, используя тип данных GUID.
Введение
Вообразите следующее: Вы анализируете 50 самых эффективных источников прибыли вашей компании. Ваша компания — многонациональная, имеет крупные филиалы на пяти континентах и головной офис в Северной Америке. Филиалы специализируются на различной продукции и все шесть офисов, включая ваш, используют разные СУБД. Например, филиал в Токио хранит свои данные в базе данных Microsoft® Visual FoxPro®, Лондонский использует Oracle, в Буэнос-Айресе данные хранятся в системе Sybase, головной офис пользуется Microsoft® SQL Server™, а филиалы в Йоханнесбурге и Канберре используют совместимые с OLE DB продукты других разработчиков. Как вам, находясь в головном офисе, собрать все эти данные в единый отчет?
Задача выглядит трудновыполнимой? Вовсе нет, если вы работаете с новым Microsoft SQL Server 7.0 и средствами Microsoft® Visual Studio® 6.0 Database Tools! Все, что вам нужно — воспользоваться средствами подключения серверов сервера и предложения TOP в конструкторе запросов Query Designer.
Конечно, это потребует дополнительных усилий, однако благодаря Visual Studio 6.0 Database Tools вы ощутите преимущества широчайших функциональных возможностей SQL Server 7.0 и получите более простой и эффективный способ решения задач по обработке корпоративных.
Прежде всего
Вы сможете воспользоваться информацией, изложенной в данной статье, если у вас имеются следующие продукты:
· Visual Studio 6.0 Database Tools и SQL Server 7.0
· Visual Studio 6.0 Database Tools и SQL Server 7.0 с обновленными Client Tools
Для обновления Database Tools при установке SQL Server 7.0:
· Если SQL Server 7.0 установлен на сервере, а Visual Studio 6.0 Database Tools — на вашем клиентском компьютере, запустите программу установки SQL Server 7.0 на своем компьютере и выберите опцию Client Tools installation.
· Если на компьютере установлены и Visual Studio 6.0 Database Tools, и SQL Server, мастер установки SQL Server 7.0 автоматически обновит Database Tools.
Доступ к внешним источникам данных OLE DB
Для доступа к разнородным источникам данных OLE DB и организации обработки корпоративной информации предназначено мощное и гибкое средство — подключенный сервер.
Подключенный сервер — это способ определения внешних объектов-данных для SQL Server 7.0 и указания информации, необходимой для организации доступа к ним. Такое определение представляет собой имя, состоящее из четырех частей, включая имя сервера внешних объектов-данных, каталог или базу данных, содержащие объект, схему или владельца объекта, а также имя самого объекта (таблицы или представления базы данных).
Для образования такого имени используется следующий синтаксис:
linked_server_name.catalog.schema.object_name
Определив подключенный сервер, вы можете использовать это имя, состоящее из четырех частей, в операторах Transact-SQL для ссылки на объекты данных подключенного сервера. Вы можете обращаться к таблицам и представлениям внешних источников данных непосредственно из операторов SELECT, INSERT, UPDATE и DELETE языка Transact-SQL.
Вот простой оператор SELECT, который, применительно к нашей многонациональной компании, может выглядеть следующим образом:
SELECT *
FROM johann_sa.svs.dbo.products
где:
· johann_sa — имя сервера в филиале в Йоханнесбурге.
· svs — имя базы данных.
· dbo — владелец объекта.
· products — имя таблицы.
Когда клиентское приложение выполняет распределенный запрос, SQL Server анализирует команду и посылает OLE DB запросы в форме набора строк — совокупности строк, состоящих из колонок данных. Так как провайдеры OLE DB используют представление данных в виде наборов строк, им известно, что нужно делать при получении таких запросов от SQL Server.
В Database Tools возможность работы с подключенным сервером доступна из панели SQL.
Применительно к нашей многонациональной компании, для получения информации о чистой прибыли из таблицы продуктов базы данных, хранящей информацию об услугах филиала в Йоханнесбурге, ваш оператор SELECT мог бы выглядеть следующим образом:
SELECT Prod.name, Prod.net
FROM johann_sa.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
ORDER BY Prod.net desc
В результирующем наборе будут перечислены размеры чистых прибылей, величиной более 1 миллиона долларов, полученных от реализации всех продуктов, с которыми работает филиал в Йоханнесбурге, за финансовый год, заканчивающийся 30 июня 1998 г. Продукты будут отсортированы по величине чистой прибыли в убывающем порядке — от самых прибыльных к менее прибыльным.
Ограничение размеров результирующих наборов
Расширим наш воображаемый сценарий. Что, если филиал в Йоханнесбурге, обслуживающий Африку и Ближний Восток, предлагает несколько сотен продуктов, причем большинство из них популярны и приносят хорошую прибыль? Как ограничить размер списка, который вы получите?
Это очень просто: используйте предложение TOP. Предложение TOP служит для ограничения количества строк, которое вы получите, если существует вероятность того, что результирующий набор будет слишком велик. С помощью ключевого слова TOP вы указываете, что желаете получить только первые n строк результирующего набора. Либо, в сочетании с ключевым словом PERCENT, вы можете указать долю результирующего набора в процентах.
Таким образом, вы используете предложение TOP в операторе SELECT одним из следующих двух способов:
SELECT TOP n *
SELECT TOP n PERCENT *
Возвращаясь к запросу в Йоханнесбург, давайте используем предложение TOP для ограничения величины списка 50-ю строками. Тогда запрос будет выглядеть следующим образом:
SELECT TOP 50 Prod.name, Prod.net
FROM johann_sa.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
Чтобы использовать предложение TOP в Database Tools, щелкните по ярлыку Query (Запрос) в окне Properties (Свойства). Если вы хотите указать определенное количество строк, введите целое число в поле Top, и соответствующий запрос будет сгенерирован. Если вы хотите указать долю в процентах, вам следует ввести в поле Top число, а затем слово "percent".
Для запроса в Йоханнесбург все, что вам потребуется сделать, это ввести число 50 в поле Top на закладке Query окна Properties.
Сортировка строк в ограниченном результирующем наборе
Если в операторе SELECT, использующем предложение TOP, вы также употребили предложение ORDER BY, SQL Server, прежде всего, сформирует полный результирующий набор в соответствии с указанным порядком сортировки, а затем извлечет из него первые n строк или соответствующую процентную долю.
Предположим, что запрос в Йоханнесбург выглядит так:
SELECT TOP 50 Prod.name, Prod.net
FROM johann_sa.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
ORDER BY Prod.net desc
Сначала SQL Server получит список всех продуктов и чистых прибылей, превышающих 1 миллион долларов, затем упорядочит его по убыванию. И лишь после сортировки будут извлечены первые 50 строк, которые будут возвращены в качестве результирующего набора.
Повышение эффективности работы с базой данных
Если вы обращаетесь с запросом к большой базе данных, приобретает большое значение производительность. В SQL Server 7.0 есть две возможности, которые вы можете использовать в Database Tools для улучшения выполнения операций с базой данных. Группы файлов повышают скорость их выполнения, а тип данных GUID обеспечивает глобальную уникальность объектов баз данных, что полезно при реализации корпоративных проектов, в частности, международных.
Усовершенствованный доступ к базам данных
Группа файлов (filegroup) — административный механизм размещения файлов базы данных, позволяющий создавать базы данных на нескольких дисках.
При создании базы данных в SQL Server 7.0 сервер автоматически относит все файлы к группе по умолчанию. До тех пор, пока вы не укажете другую группу, в качестве группы по умолчанию принимается группа Primary (Главная). (Вы можете изменить группу файлов, используемую по умолчанию при создании новых файлов в базе данных и после ее формирования). Однако, даже если вы указали, что по умолчанию должна использоваться другая группа, все системные файлы вашей базы данных останутся в группе Primary.
Группа файлов, назначенная базе данных, отслеживает эти файлы, даже если они размещены на разных дисках.
Например, вы указываете принадлежность таблицы к одной группе файлов, но сохраняете файлы индексов на отдельном диске. При обращении к таблице для извлечения данных ваш запрос «распределяется» по двум дискам, и несколько операций поиска в различных файлах таблицы происходят одновременно, что ускоряет получение результирующего набора.
Использование групп файлов облегчает выполнение работ по администрированию баз данных. Вам не обязательно выполнять резервирование сразу всей базы данных. Вместо этого вы можете производить резервирование в несколько приемов по группам файлов.
В Database Tools вы указываете группу файлов для таблицы и текста в полях Table и Text на странице Tables Property Page. Группа файлов для индексов указывается на странице Indexes/Keys Property Page.
При планировании использования групп файлов в вашей базе данных необходимо учитывать следующее:
· Группы файлов не могут быть созданы отдельно от файлов базы данных.
· Группа файлов не может использоваться более чем одной базой данных.
· Только одна группа может использоваться по умолчанию для базы данных в любой момент времени.
· Файл может относиться только к одной группе.
· После того, как файл добавлен к базе данных и отнесен к некоторой группе, вы не можете переместить его в другую группу.
· Системные объекты и файлы всегда относятся к группе Primary.
Для углубленного изучения групп файлов обратитесь к документации по SQL Server 7.0.
Обеспечение уникальности объектов-данных
Если вы имеете дело с большими базами данных, которые используются несколькими территориальными подразделениями, как в случае нашей воображаемой международной корпорации, вы можете столкнуться со сложной проблемой обеспечения уникальности табличных объектов. В таком случае подходящим решением может стать UNIQUEIDENTIFIER — тип данных GUID, предназначенный для хранения глобальных уникальных идентификаторов. GUID представляет собой двоичное число, заведомо уникальное — никакой другой компьютер никогда не сгенерирует то же самое значение.
Уникальное значение GUID вырабатывается на основании идентификационного номера сетевого адаптера и уникального числа, получаемого из тактового генератора центрального процессора. Производители сетевых адаптеров гарантируют уникальность каждого из них, по крайней мере, в течение ближайшего столетия.
Значения UNIQUEIDENTIFIER не генерируются автоматически, как в случае свойства IDENTITY. Чтобы получить значение UNIQUEIDENTIFIER для объектов вашей таблицы, вы должны указать функцию NEWID в качестве значения столбца по умолчанию.
Например, если вы хотите создать таблицу с перечислением чистых прибылей для всех самых прибыльных продуктов всех филиалов вашей международной компании и использовать тип данных GUID, вам следует применять следующую конструкцию:
CREATE TABLE NetRevenueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10))
В Database Tools это делается средствами Database Diagram или при проектировании таблицы. Если вы хотите использовать уникальную идентификацию, выберите для соответствующего столбца опцию Is RowGUID. В качестве значения по умолчанию будет установлено (newid()), и уникальные идентификаторы RowGUID будут генерироваться автоматически.
Хотя существует множество ситуаций, когда вы хотели бы обеспечить уникальность объектов таблицы, следует учитывать следующие особенности типа данных UNIQUEIDENTIFIER:
· Большая длина и отсутствие наглядности.
· Значения случайны и не подчиняются закономерности, которую пользователь был бы в состоянии осмыслить.
· Значения трудно использовать в приложениях, ориентированных на работу с последовательно изменяющимися величинами (порядковыми номерами и т.д.).
· Для хранения величин требуется 16 байт (это много), поэтому индексы, использующие их в качестве ключа, могут быть медленными.
За более подробной информацией о типе данных GUID обратитесь к документации по SQL Server 7.0.
Получение данных для отчета
В целом, новые средства SQL Server 7.0, которыми вы можете пользоваться из Database Tools, позволяют быстрее и эффективнее решать задачи, связанные с корпоративными базами данных и подобные случаю с воображаемой международной компанией.
Используя подключенный сервер и предложение TOP, вы сможете получить доступ к данным из внешнего источника OLE DB и ограничить размеры потенциально слишком длинного результирующего списка так, чтобы вам было удобно его обрабатывать. В добавок, эти операции в конечном счете могут быть легче реализованы и выполняться эффективнее, благодаря уместному использованию групп файлов и типа данных GUID.
Итак, возвращаясь к нашему воображаемому сценарию, как вам получить результаты, которые послужат основой для анализа прибыли?
Ниже приведен оператор SELECT, который вы можете использовать для базы данных в вашем головном офисе в Чикаго:
SELECT TOP 50 NetRevenueTable.name, NetRevenueTable.net
FROM
(
SELECT Prod.name, Prod.net
FROM johann_sa.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
UNION
SELECT Prod.name, Prod.net
FROM tokyo_jn.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
UNION
SELECT Prod.name, Prod.net
FROM london_uk.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
UNION
SELECT Prod.name, Prod.net
FROM buenosa_ag.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
UNION
SELECT Prod.name, Prod.net
FROM canberra_au.svs.dbo.products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
UNION
/*Следующий оператор SELECT предназначен для получения данных из базы данных svs в головном офисе в Чикаго */
SELECT Prod.name, Prod.net
FROM products AS Prod
WHERE date between '07/01/97' and '06/30/98'
AND Prod.net > 1
)
as NetRevenueTable
ORDER BY NetRevenueTable.net desc
Результат должен дать вам представление об основных источниках прибыли вашей компании — это хорошая отправная точка для вашего анализа. Желаем вам успехов!