Microsoft SQL Server 2000: новые возможности для
разработчика приложений-II

© Наталия Новакова
Статья была опубликована в КомпьютерИнформ, №6-2001

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

В SQL Server 2000 имеется 3 типа пользовательских функций: Scalar, Multi-statement, InLine. Скалярная функция может возвращать значения любого скалярного типа данных, кроме данных типа text, image, table. Такая функция может объединять несколько команд языка T-SQL, находящихся в блоке BEGIN…END, например,

USE pubs
GO
CREATE FUNCTION FunState
(@a varchar(20))
RETURNS varchar(20)
BEGIN
IF @a IS NULL
SET @a = "Not Applicable"
RETURN @a
END

Обратиться к такой функции можно с помощью конструкции select, указав вместо поля таблицы значение функции с параметром:

SELECT pub_name, City, dbo.FunState(state)
AS State,Country
FROM dbo.publishers

Другой тип UDF - Multi-Statement Table-valued Function. Как следует из названия, этот тип функции возвращает тип даных table. Тело такой функции может быть достаточно сложным и включать множество операторов, находящихся между ключевыми словами BEGIN…END. В данном простом примере в БД Pubs создается функция, которая может возвращать фамилию и имя либо автора книги, либо служащего издательства.

USE pubs
GO
CREATE FUNCTION FunMult
@b nvarchar(8))
RETURNS @Fun_Auth table
([First Name] nvarchar(80) not null,
[Last Name]nvarchar(80) not null)
AS
BEGIN
IF @b = "author"
INSERT @Fun_Auth SELECT au_fname, au_lname
FROM authors
ELSE IF @b = "employee"
INSERT @Fun_Auth SELECT fname, lname
FROM employee
RETURN
END

В зависимости от входного параметра, указываемого в конструкции select при вызове функции, получаем разный результат, обращаясь к таблице author или к employee:

SELECT * FROM dbo.FunMult("author")
SELECT * FROM dbo.FunMult("employee")

Функция InLine тоже возвращает значение типа table, но отличается от Multi-Statement Table-valued тем, что может состоять только из одной команды select.

USE pubs
GO
CREATE FUNCTION FunInLine
@State nvarchar(30))
RETURNS table
AS
RETURN (
SELECT pub_name, city
FROM Pubs.dbo.publishers
WHERE state = @State)

Обращение к функции происходит в предложении from конструкции select:

SELECT * FROM FunInLine(N'NY')

Особенностью функции InLine является то, что код функции при выполнении программы вставляется непосредственно в исполняемый набор команд. Другими словами, происходит не вызов функции, а встраивание. Основное отличие UDF от хранимых процедур заключается в том, что функция обязательно должна вернуть хотя бы какое-то значение. К сожалению, UDFs имеют некоторые ограничения. Нельзя, например, изменять данные в таблицах БД, выводить данные с помощью команд print и select. Внутри UDFs нельзя использовать недетерминированные функции типа GETDATE().

В Microsoft SQL Server 2000, помимо традиционных типов ограничений целостности, появилось дополнительное, позволяющее контролировать, удаление или изменение данных в связанных таблицах. Часто требуется автоматическое выполнение изменений в зависимых таблицах при изменении данных в главной таблице. В SQL Server 7.0 это можно было реализовать с помощью специально для этих целей написанных триггеров. Главная задача таких триггеров как раз и заключалась в том, чтобы отобразить изменения данных в основной таблице на все подчиненные. В SQL Server 2000 реализован механизм каскадных изменений данных в зависимых таблицах. Для применения этого механизма в зависимых таблицах необходимо использовать опцию CASCADE при определении столбцов. Если же поставлена опция NO ACTION, то SQL-серевер не будет предпринимать никаких действий в зависимых таблицах при изменении данных в главной таблице. Этот режим соответствует работе предшествующих версий.

Интересные изменения были произведены в работе триггеров. Триггер может применяться не только к таблице, но и к представлению, кроме представления, определенного с помощью опции WITH CHECK OPTION.

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

Существенные улучшения были внесены в работу сервера с индексами. В SQL Server 2000 было оптимизировано использование сервером системных ресурсов при построении и перестроении индексов. При построении индекса учитываются преимущества многопроцессорной архитектуры при сканировании таблиц и сортировке данных. Порядок сортировки для индексируемых столбцов можно задавать явно по возрастанию ASC или по убыванию DESC. Порядок сортировки может быть указан для любого типа индексов. В SQL Server 2000 разрешается создавать индексы на вычисляемые столбцы (computed columns).

Теперь допускается размещение промежуточных данных, используемых при построении и перестроении индексов, в БД TEMPDB. При размещении пользовательских БД и БД TEMPDB на разных физических дисках можно существенно уменьшить время, связанное с созданием индекса. При использовании в команде создания индекса опции SORT_IN_TEMPDB сервер будет использовать для построения индексов и выполнения промежуточной сортировки БД TEMPDB. Представления теперь можно индексировать! Традиционное, неиндексированное представление - это лишь определение "увековеченного" SQL-запроса, который выполняется всякий раз при обращении к данному представлению. Как только над представлением создается индекс, его результаты "материализуются" и обновляются при модификации данных в исходных таблицах.

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

О нюансах работы с SQL Server 2000 вы можете узнать на авторизованных курсах Microsoft № 2071, 2072, 2073, посвященных администрированию и программированию SQL Server 2000.

 

Дополнительная информация