Microsoft SQL Server 2000. Новые возможности для разработчика приложений. Часть 1

Пользуясь случаем, я хотел бы поблагодарить слушателей секции «Базы данных», чьи вопросы, отзывы, критические замечания и просто пожелания и проявленный интерес во многом способствовали формированию окончательного облика статьи. Не ставя перед собой цель, дать исчерпывающий обзор функциональности следующей версии SQL Server (которая на момент подготовки материала находилась в стадии второго бета-тестирования), я постараюсь сконцентрироваться на тех новых возможностях, которые предоставляет SQL Server 2000 разработчикам клиент-серверных и многоуровневых приложений в плане программирования серверной части систем. Кроме того, в статье приводится базовый код, использованный при подготовке демонстрационных примеров к докладу. Везде, где это не оговорено особо, в примерах подразумевается использование модельной базы данных Northwind, устанавливаемой вместе с SQL Server и специально предназначенной для экспериментов. Таким образом, статью можно рассматривать и как практическое руководство, полезное для максимально быстрого освоения новых возможностей SQL Server 2000 тем более, что они позволяют существенно повысить эффективность существующих приложений и с нетерпением ожидались разработчиками.

Microsoft SQL Server 2000 заслуживает внимания хотя бы потому, что этот продукт предоставляет комплексную функциональность реляционного сервера баз данных со встроенными механизмами создания распределенных систем разнообразных степеней автономности, служб преобразования данных и интеграции с СУБД других производителей; аналитического сервера (OLAP), обладающего собственным многомерным хранилищем с поддержкой функций принятия решений (decision support) и поиска закономерностей (data mining). Для всех перечисленных типов задач используются открытые интерфейсы, что позволяет разработчикам создавать пользовательские приложения любого уровня сложности с применением широчайшего спектра инструментов программирования. SQL Server 2000 хорошо масштабируется и способен обеспечивать поддержку как баз данных уровня корпорации (Windows 2000 DataCenter, 32-процессорные комплексы, адресация до 64 Гбайт оперативной памяти), так и персональных баз данных. К концу года планируется выпустить SQL Server 2000 для Windows СЕ. Как было объявлено, после выхода 64-разрядной версии Windows 2000, SQL Server станет первым продуктом из семейства Microsoft BackOffice, для которого будет выпущена 64-разрядная версия.

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

Multiinstancing

Термин «Multiinstancing» означает возможность установки и одновременной работы нескольких экземпляров (instances) SQL Server 2000 на одной машине. Например, на ноутбуке Dell Latitude, на котором пишутся эти строки, установлено три экземпляра SQL Server: один - версии 7.0 и два - версии 2000 (кстати, ее внутренний номер - 8.0, как показывает @@VERSION), один из которых корпоративной редакции, а другой - стандартной. Искушенные читатели наверняка знают, что путем некоторых манипуляций можно было заставить на одной машине относительно мирно сосуществовать несколько экземпляров одной из предыдущих версий. Однако этот способ нельзя назвать каноническим, он накладывает дополнительные ограничения, и я не буду останавливаться на нем подробно. Академическое же решение позволяло, начиная с версии 7.0, устанавливать ее вместе с версией 6.5 или 6.0; к сожалению, в каждый момент времени могла быть активна только одна из них. Версия 2000 преодолевает эти ограничения: на сервер с Windows 2000 (или Windows NT 4.0) разрешается установить столько экземпляров SQL Server 2000, сколько выдержат ресурсы сервера. Каждый из них является полноценным сервером баз данных. Один из экземпляров может быть назначен экземпляром по умолчанию (default instance), в этом случае его имя совпадает с именем сервера. К другим обращение происходит по имени <имя сервера><имя экземпляра>, где имя экземпляра задается в процессе установки. Именно эту строку возвращает функция @@SERVERNAME = SERVERPROPERTY(?ServerName?). Параметр ?InstanceName? заставляет последнюю вернуть только имя экземпляра. В качестве экземпляра по умолчанию может выступать версия 8.0 или 7.0. При этом на машине может быть также установлена версия 6.5 или 6.0, переключение на которую осуществляется с экземпляра по умолчанию подобно тому, как это делалось в 7.0.

Новые типы данных

BIGINT

По сравнению с типом INT, который представляет двойное слово (long), BIGINT имеет в два раза большую длину (64 бит) и служит для представления знаковых целых в диапазоне от -2^63 до 2^63-1. Пример приведен в Листинге 1.

Здесь явное преобразование cast(-2 as bigint) потребовалось по той причине, что тип INT оставлен целым типом по умолчанию из соображений совместимости, а функция степени, как известно, возвращает значение того же типа, что и ее аргумент.

BIGINT может использоваться для типизации как переменных, так и полей. Пример приведен в Листинге 2.

Следующий оператор insert в #tblTypesTest, очевидно, вызовет переполнение, так как наибольшее возможное значение «длинного целого» достигнуто. Однако на практике такая ситуация нетипична, так как при использовании поля BIGINT в качестве индекса с начальным значением 1 и шагом 1, если даже заполнять таблицу с максимальной достижимой на сегодня производительностью SQL Server, нам потребуется

9223372036854775807/(227E3*60*24
*365) = около 77 млн лет.

В Transact-SQL появились новые функции, такие, как COUNT_BIG() и другие, возвращающие в отличие от своих предшествующих аналогов (СOUNT() и др.) значения типа BIGINT.

TABLE

В SQL Server 2000 локальные переменные могут иметь тип «таблица». Определение полей таблицы производится аналогично командам CREATE / ALTER TABLE. Из ограничений допустимы только PRIMARY KEY, UNIQUE и NULL / NOT NULL. Табличные переменные не могут иметь индексов, кроме тех, которые создаются автоматически для поддержки ограничений PRIMARY KEY и UNIQUE. Пример объявления переменной типа «таблица» приведен в Листинге 3.

Теперь переменная @t может участвовать во всех операциях, традиционно связанных с таблицами, кроме операций массового копирования SELECT ... INTO и INSERT ... EXEC. Таким образом, наполнять таблицу-переменную приходится постепенно, запись за записью. Пример переноса записей из таблицы Customers в переменную @t дан на Листинге 4.

Кроме операций вставки над @t допустимы все остальные основные табличные операции обновления и чтения данных (см. Листинг 5).

Поля таблиц не могут иметь тип TABLE, так что реализовать вложенные таблицы подобным способом не удастся. Табличные переменные можно рассматривать как своего рода альтернативу локальным временным таблицам. В отличие от временных таблиц, табличные переменные не разрешается применять в операторах DDL: ALTER TABLE, DROP TABLE и т. д., а также TRUNCATE TABLE, создавать на них индексы и проч. Областью действия локальных временных таблиц служит вся сессия от момента создания таблицы, в то время как таблица-переменная (как и любая локальная переменная) существует только внутри текущего модуля, где она была определена. Использование табличных переменных позволяет избежать лишних затрат на перекомпиляцию. Это легко проверить. Создадим хранимую процедуру

CREATE PROCEDURE MySP AS
create table #t (f int)
select * from #t

Выполним ее, отслеживая события SP:StmtStarting, SP:StmtCompleted и SP:Recompile через Profiler. Увидим, что наличие оператора DDL вызывает рекомпиляцию процедуры перед чтением таблицы. Заменим временную таблицу на табличную переменную

declare @t table (f int)
select * from @t

и повторим эксперимент. Перекомпиляции больше не происходит.

SQL_VARIANT

Переменные этого типа могут принимать значения любого скалярного типа данных, определенного в Transact-SQL, кроме text, ntext, image и timestamp. В качестве иллюстрации определим переменные самых разных базовых типов и присвоим им произвольные значения (см. Листинг 6).

Определим переменную вариантного типа

declare @x as sql_variant

после чего ей можно присваивать любую из ранее объявленных переменных:

set @x = @i
set @x = @d
set @x = @f

На самом деле, базовый тип присвоенного значения хранится внутри вариантной переменной, и его можно посмотреть с помощью системной функции sql_variant_property, где в качестве параметра передается имя того свойства, значение которого нас интересует. Некоторые из этих свойств являются общими для всех базовых типов, например свойство ?BaseType?; другие имеют смысл в зависимости от типа, например ?Precision? или ?Collation?. Чтобы увидеть все свойства вместе, нужно создать очень простую пользовательскую функцию (см. Листинг 7), которая позволит анализировать содержимое вариантной переменной:

select * from fnShowVariantProps(@x)

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

declare @y as sql_variant, 
@z as sql_variant
select @y = 2, @z = 3
select @y * @z

последняя строка завершится с ошибкой, потому что из предшествующего описания не вытекает однозначно, какой базовый тип предполагается в операции умножения: int, smallint, tinyint, decimal, float и т. д. Сделать это требуется в явной форме:

select cast(@y as int) * 
cast(@z as int)

Попытаюсь пояснить, когда и зачем стоит применять тип SQL_VARIANT, на следующем простом примере. Предположим, у меня есть некий класс со свойствами разных типов, многие из которых не являются обязательными. В таблице #MyObjects я создам репозиторий объектов этого класса (см. Листинг 8). Коль скоро объектам разрешено иметь неинициализированные свойства, то такая таблица может оказаться довольно разреженной (см. Листинг 9).

Для экономии места лучше воспользоваться типом SQL_VARIANT (см. Листинг 10). Хранение прежних свойств в новой структуре приводит к более экономному расходованию пространства.

Тип SQL_VARIANT может использоваться в индексах (если длина ключа не превосходит 900 байт). Он не может выступать в качестве IDENTITY, однако способен участвовать в ограничениях типа PRIMARY / FOREIGN KEY.

Определяемые пользователем функции

В SQL Server 2000 помимо хранимых процедур появились хранимые функции, в связи с чем в Transact-SQL были добавлены операторы СREATE / ALTER / DROP FUNCTION. Существует несколько разновидностей определяемых пользователем функций (user-defined functions, UDFs).

Скалярные функции

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

Табличные функции

Вторая разновидность представляет собой функции, возвращающие значение типа TABLE. Для иллюстрации слегка модифицируем предыдущий пример и поставим задачу возврата не просто количества слов в строке, а списка самих этих слов. Описание подобной функции отличается от описания скалярной главным образом тем, что в качестве возвращаемого значения фигурирует тип «таблица», причем его описание практически аналогично рассмотренным выше табличным переменным. Естественно, что функции этого вида могут присутствовать везде, где в традиционных операторах SQL допускается применение таблиц (см. Листинг 12).

In-line-функции

Дополнительный «подвид» табличных функций образуют так называемые функции in-line table-valued. От обычных табличных функций их отличает то, что вся функция состоит из одного оператора RETURNS, в котором непосредственно порождается результирующий набор записей. Описания структуры таблицы в этом случае не требуется. Выше уже был приведен пример такой функции, где с помощью функции fnShowVariantProps анализировались свойства переменных SQL_VARIANT. Вообще, по своей природе этот подвид очень напоминает обычные представления. Допустим, требуется подготовить отчет по продажам зерновых на основе данных из базы данных Northwind. Это можно сделать с помощью обычного представления, как показано в Листинге 13, или с помощью функции in-line (см. Листинг 14).

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

select * from fnSalesByProductCategory
(?Grains?)

либо вообще по всем категориям:

select * from fnSalesByProductCategory
(??)

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

Некоторые функции in-line в базе данных master имеют владельцем не dbo, а SYSTEM_FUNCTION_SCHEMA. Такие функции можно вызывать в сокращенной форме, указывая вместо master.system_function_schema просто «::», например:

select * from ::fn_helpcollations()

Ограничения UDFs

Теперь немного о грустном, а именно о том, чего не могут определяемые пользователем хранимые функции. Во-первых, в качестве входных параметров можно передавать только скалярные типы данных; курсоры или таблицы для этого не годятся. Во-вторых, возвращаемое значение должно быть либо скаляром, либо таблицей. Невозможно, например, вернуть из функции значение типа «курсор». В-третьих, существуют значительные ограничения на операторы в теле функции. Так, нельзя использовать операторы EXEC <хранимая процедура> или EXEC ?строка?. Из функций разрешено вызывать только встроенные, причем лишь те, которые являются детерминированными, т. е. всякий раз при одинаковых входных параметрах дают один и тот же результат. Это означает, что внутри пользовательской функции нельзя, например, задействовать встроенные функции RAND(), GETDATE() и др. Не допускается использование операторов DDL, а также операторов начала и завершения транзакции. Невозможно вносить изменения в таблицы, если только эти таблицы не являются переменными, локальными по отношению к функции. Нельзя использовать глобальные курсоры, однако внутри функции можно создавать локальные и работать с ними. Не существует понятия временной хранимой функции. Данные ограничения связаны с принципом детерминизма, о котором речь пойдет ниже.

Каскадная ссылочная целостность

В предыдущих версиях SQL Server была реализована только строгая ссылочная целостность, когда нельзя ни удалить запись из родительской таблицы, ни изменить у нее значение первичного ключа до тех пор, пока она имеет ссылающиеся на нее записи в одной из дочерних таблиц. В SQL Server 2000 была добавлена поддержка каскадной ссылочной целостности для операций удаления и модификации. В Transact-SQL ограничение типа внешнего ключа теперь имеет операторы ON DELETE {CASCADE | NO ACTION} и ON UPDATE {CASCADE | NO ACTION}, где NO ACTION соответствует строгой целостности. Аналогичные возможности существуют в графическом интерфейсе Enterprise Manager. Чтобы показать, как они работают, создадим рабочие таблицы, содержащие список клиентов, сделанные ими заказы и детали по каждому заказу, связанные как Cust -> Ords -> OrdDet (см. Листинг 15).

Обе связи поддерживают каскадное удаление и обновление. Отметим, что клиент с ID = ALFKI сделал заказы №№10643, 10692, 10702, 10835, 10952 и 11011. Удаляем его из таблицы Cust и обращаем внимание, что вместе с ним из таблицы заказов, дочерней по отношению к клиентам, автоматически удалены все заказы, сделанные данным клиентом, а из таблицы деталей заказов, дочерней по отношению к таблице заказов, также исчезли все записи, относящиеся к заказам, сделанным данным клиентом (см. Листинг 16).

Изменим поведение FOREIGN KEY для третьей таблицы с каскадного на соответствующее предыдущим версиям и попробуем удалить очередного клиента (см. Листинг 17). Эта операция вызывает ошибку, так как мы сохранили каскадную связь между Cust и Ords, но разорвали ее между Ords и OrdDet, в результате чего ни в одной из связанных таблиц данные удалены не будут.

Аналогично работает и каскадное обновление, предложение

update Cust set CustID = ?AAAAA? where
 CustID = ?ANATR?

приводит к автоматическому обновлению поля CustID в таблице Ords.

В связи с появлением каскадной ссылочной целостности были внесены изменения в функции, возвращающие метаданные. Так, OBJECTPROPERTY «понимает» два новых параметра - ?CnstIsDeleteCascade? и ?CnstIsUpdateCascade?, где возврат значения 0 соответствует поведению No Action, а 1 - Cascade. В служебном представлении INFORMATION_ SCHEMA.REFERENTIAL_CONSTRAINTS появились два новых поля - update_rule и delete_rule (см. Листинг 18).

Новое в триггерах

Порядок срабатывания триггеров

SQL Server 7.0 позволил определять несколько триггеров на одно действие. Однако в документации подчеркивалось, что в зависимости от обстоятельств триггеры могут вызываться в произвольной последовательности, так что реализованная в триггере бизнес-логика «не знает», перед какими или после каких оставшихся триггеров будет вызван данный триггер. Это положение приводило к тому, что множественные триггеры на событие рассматривались, в основном, как средство структурного оформления кода. В разные триггеры оформлялись независимые задачи, различные по своим бизнес-функциям. В SQL Server 2000 появилась возможность назначать, какой из триггеров заведомо будет срабатывать первым, а какой последним, что позволяет хотя бы частично решать вопросы размещения в триггерах задач, связанных друг с другом ходом выполнения. Для иллюстрации сказанного, как всегда, создадим рабочую таблицу, «повесим» на нее четыре триггера на UPDATE и обновим некоторые записи в таблице Cust (см. Листинг 19).

Как уже было сказано, порядок вызова триггеров предсказать нельзя. Скорее всего, они будут срабатывать в порядке создания. Пусть CustTr4 будет первым триггером в последовательности их вызова, а CustTr1 - последним (см. Листинг 20).

Повторим предыдущий UPDATE и убедимся, что теперь это действительно так. Параметр @order может также принимать значение ?none?, что переводит триггер в состояние произвольного порядка срабатывания. Параметр @stmttype указывает, на какое действие устанавливается очередность триггера (например, в том случае, если он был определен одновременно на несколько операций).

Уже знакомая нам функция OBJECTPROPERTY позволяет определить, является ли триггер первым, последним или промежуточным в последовательности вызова (см. Листинг 20).

Триггеры INSTEAD OF

Как известно, в зависимости от времени срабатывания триггеры делятся на «претриггеры» (BEFORE), «вместотриггеры» (INSTEAD OF) и «послетриггеры» (AFTER). В предыдущих версиях SQL Server по 7.0 включительно поддерживались только AFTER-триггеры. Как следует из названия, эти триггеры срабатывают уже после того, как произошло основное событие, на которое они были определены. Таким образом, все ограничения в схеме данных срабатывают раньше, что, например, существенно усложняло задачу пользовательской реализации каскадной ссылочной целостности в предыдущих версиях, так как единственно возможные там ограничения строгого типа препятствовали завершению операции. Как мы уже видели, в SQL Server 2000 каскадная ссылочная целостность может быть задана декларативно в составе самих ограничений внешнего ключа. Кроме того, в нем поддерживаются триггеры INSTEAD OF, срабатывающие вместо исходной операции, вызвавшей включение триггера. Соответственно, при создании или изменении триггера теперь требуется указать его тип: CREATE / ALTER TRIGGER … [FOR] [AFTER | INSTEAD OF] …, где AFTER означает традиционный для ранних версий «послетриггер». Основные отличия в использовании разных типов триггеров в SQL Server 2000 сведены в Таблицу 1.

В целом триггеры INSTEAD OF могут быть полезны, когда задается ссылочная целостность с логикой, более сложной, чем строгая или декларативная, а также для создания обновляемых представлений, традиционно не являющихся таковыми. Думаю, что на последнем аспекте следует остановиться более подробно. Я не собираюсь сейчас давать каноническое определение обновляемого представления. Поборники строгих формулировок могут обратиться к документации. Поступим проще. Вспомним, что SQL Server априори «не знает» ничего о том, как будут распределяться изменения, внесенные в представление, по исходным таблицам. Если из определения представления это однозначно вытекает, его можно считать обновляемым. Поясню на примере. Создадим экспериментальную табличку и представление (см. Листинг 21), которое, очевидно, обновляемым являться не будет, так как при всех своих достоинствах SQL Server не в состоянии читать мысли и не может догадаться, что в поле Name нужно взять слово, стоящее слева от пробела, и положить его в поле FirstName таблицы Cust, а слово справа - в поле LastName. В этом можно убедиться, выполнив:

update vwEmpl set Name = 
?Don Pedro?
 where ID = 1
или
insert vwEmpl (Name, Title) 
values(?Donna Rosa?,
 ?Your aunt?)

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

delete vwEmpl where ID = 2

Теперь нужно разъяснить SQL Server, что от него требуется в случае обновления и вставки в vwEmpl. Это можно сделать при помощи триггеров INSTEAD OF (см. Листинг 22).

Триггеры осуществляют разбор поля Name на имя и фамилию, которые помещаются непосредственно в таблицу Empl, что немедленно отражается в представлении vwEmpl. Нужно иметь в виду, что внутри триггеров INSTEAD OF доступны таблицы inserted и deleted, имеющие структуру в соответствии с представлением. Повторим эксперимент с вставкой и обновлением и убедимся, что теперь все работает в соответствии с нашим замыслом, так как реально вместо них работают наши триггеры trEmplIns и trEmplUpd соответственно (см. Листинг 23).

Для получения метаданных по триггерам по-прежнему можно задействовать системные хранимые процедуры, а функция OBJECTPROPERTY позволяет определить тип триггера (см. Листинг 24). Пример поиска триггеров, заданных для таблицы представления, приведен в Листинге 25.

Продолжение следует…

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

АЛЕКСЕЙ ШУЛЕНИН

Microsoft Sales Specialist

Microsoft Certified Systems Engineer

Microsoft Certified Database Administrator

Microsoft Certified Solutions Developer

системный инженер отдела бизнес-приложений российского представительства Microsoft. Имеет сертификаты MCSE, MCDA, MSS. С ним можно связаться по адресу: rusdev@microsoft.com.