Эта статья была опубликована на сайте www.optim.ru
Журнал Технология клиент/сервер №3, 2000

Итак, время SQL Server 7.0 истекло. На смену ему пришел Microsoft SQL Server 2000 — следующая версия сервера БД от фирмы Microsoft. Эта новая версия во многом похожа на S0L Server 7.0, и, возможно, если бы не страсть к переименованиям, ее можно было бы назвать версией 7.х.

Наиболее интересными для разработчиков будут улучшения в реляционном механизме. Эти усовершенствования расширяют функциональность СУБД, превращая SQL Server 2000 в прекрасную среду для разработки приложений, и мы в этой статье уделим большое внимание улучшениям программируемости SQL Server 2000 на Transact-SQL. Примеры в тексте основаны на поставляемом вместе с SQL Server БД Northwind.

Другая тема для разговора – индексированные view, они же Indexed View. Это нововведение уже знакомо пользователям некоторых других РСУБД, например, Oracle 8. Оно позволяет значительно ускорить выполнение повторяющихся запросов к нечасто обновляемым данным.

Кроме изменений в Transact-SQL в новой версии сделано множество внутренних изменений, служащих для улучшения ПМН (производительности, масштабируемости и надежности). Теперь поддерживаются до 32 процессоров и до 64GB памяти, параллельные операции DBCC (database consistency checking) и индексирования, улучшены резервное копирование и восстановление данных, поддерживаются кластеры серверов, встроено управление серверами резерва (log shipping) и поддержка высокоскоростной технологии System Area Network (SAN).

Репликация – это область, где внесено огромное количество изменений. Если оно вам нужно, всерьез подумайте о переходе на SQL Server 2000. При транзакционной репликации, появившейся в новой версии подписчик получает обновления через транзакционную очередь сообщений. Это позволяет большую часть времени работать в режиме офф-лайн, отключившись от сети.

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

Расширения возможностей реляционной базы данных

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

В SQL Server 2000 появилось три новых типа данных — bigint, sql variant и table.

bigint 

bigint — это 64-битное целое, поддерживающее значения от -263 до 263-1. Использовать его предлагается там, где недостаточно обычного целого (231-1). Соответственно появились и новые функции: count big() и rowcount big0, которые соответствуют функции COUNT и переменной @@ROWCOUNT, но возвращают значение типа bigint.

sql_variant

sql_variant способен хранить любой стандартный тип данных, кроме text, ntext, image, timestamp и себя самого. Этот тип данных очень напоминает тип данных variant в VB, позволяя хранить в колонках, параметрах или переменных значения других типов данных. Например, колонка типа sql_variant может содержать данные типов int, decimal, char, binary или nchar. Каждый экземпляр sql_variant содержит значение данных и метаданные – тип данных, максимальный размер и т.д. Применяя этот тип данных следует помнить, что хранимые данные нужно преобразовывать к оригинальному типу с помощью функции CAST или CONVERT.

DECLARE @my_var sql_variant
SET @my_var = 3.142
DECLARE @my_chr VARCHAR(12), @my_dec DECIMAL(5,4)
SET @my_chr = CAST(@my_var AS VARCHAR(12))
SET @my_dec = CAST(@my_var AS DECIMAL(5,4))

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

SQL_VARIANT_PROPERTY(выражение, свойство),

где свойство – имя свойства SQL_VARIANT, например, BaseType, Precision, Scale, TotalBytes, Collation или MaxLength.

Этот тип данных очень полезен там, где управление метаданными можно возложить на приложение.

table

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

Этот тип данных может использоваться вместо временных таблиц, хранящихся в базе данных tempdb.

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

INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements

Чтобы определить локальную таблицу, нужно использовать обычное выражение DECLARE:

DECLARE @local_variable TABLE <table_defintion>

Покажем на примере, как это делается. Определим и заполним локальную переменную, содержащую данные о территориях из БД Northwind:

DECLARE @territory TABLE (territory_id INT, territory_desc NVARCHAR(50))
INSERT INTO @territory
SELECT TerritoryId, TerritoryDescription FROM Territories

Над переменными типа table можно выполнять операции SELECT, INSERT, UPDATE, DELETE, но операции ALTER, DROP, TRANCATE TABLE недопустимы. Основные отличия табличного типа от временных таблиц состоят в области видимости (локальная табличная переменная видна только внутри процедуры или функции, где была объявлена, но может возвращаться как out-параметр или в качестве возвращаемого значения функции) и в том, что для переменных типа table нельзя создавать неуникальные индексы.

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

Пользовательские функции

В SQL Server 2000 появилась поддержка пользовательских функций (User-Defined Functions, UDF). Такие функции давно знакомы пользователям серверов Oracle или Informix. Теперь они появились и в SQL Server. Transact-SQL расширен операторами CREATE FUNCTION, ALTER FUNCTION и DROP FUNCTION.

Пользовательские функции сходны с хранимыми процедурами. Однако, в отличие от хранимых процедур, они могут использоваться в запросах так же, как и системные функции. Как и хранимые процедуры, они могут иметь один или несколько параметров, но возвращают скалярные типы данных, например, int, decimal, varchar, sql_variant – или тип данных table.

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

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

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

Простой пример пользовательской функции, возвращающей таблицу, можно привести на основе view для БД Northwind, возвращающего продажи за 1997 год. Листинг 2 показывает, как можно создать функцию, возвращающую продажи за год, указанный как параметр функции.

Использовать эту функцию можно, так же, как и обыкновенное view. Единственное различие – теперь можно указать параметр - нужный год:

SELECT * FROM ProductSalesByYear(1996)

Расширяя этот простой пример, предположим, что нужно получить продажи за 1997 год вместе с информацией о категории, но только для сумм, превышающих $15 000. Это тоже несложно:

SELECT Product.ProductID, Product.ProductName, Product.ProductSales,
Categories.*
FROM ProductSalesByYear(1997) AS Product
INNER JOIN Categories ON Product.CategoryID = Categories.CategoryID
WHERE Product.ProductSales > 15000

Если пользовательская функция возвращает скалярный тип данных, все, что нужно – определить тип возврата и указать значение в выражении return. Листинг 1 показывает пример, возвращающий целое, представляющее число заказов, полученных в указанный день. Эту функцию можно использовать в выражениях select или set:

DECLARE @count INT
SET @count = dbo.SalesForSpecifiedDate('12/12/97')

Листинг 1. 
Пользовательская функция – продажи за указанную дату

CREATE FUNCTION dbo.SalesForSpecifiedDate (@date DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @ordercount INT
DECLARE @startdate DATETIME, Senddate DATETIME
DECLARE @txtdate VARCHAR(12)
SET @txtdate - CAST(
DATENAME(year, @date) + '/' +
CAST(DATEPART(month, @date) AS VARCHAR(2)) + '/' +
DATENAME(day, @date) AS DATETIME
)
SET @startdate - CAST(@txtdate AS DATETIME)
SET @enddate - CAST(@txtdate AS DATETIME) + 1
SET @ordercount - (SELECT COUNT(*) FROM Orders
WHERE OrderDate >- @startdate AND OrderDate < @enddate)
RETURN (@ordercount)
END
GO

Листинг 2. 
Пользовательская функция – продажи за год

CREATE FUNCTION dbo.ProductSalesByYear (@year INT = NULL)
RETURNS @Categories TABLE (
CategoryID INT,
CategoryName NVARCHAR(15),
ProductID INT,
ProductName NVARCHAR(40),
ProductSales MONEY
)
AS
BEGIN
IF @year IS NULL
SET @year - (SELECT MAX(DATEPART(year, ShippedDate)) FROM Orders)
INSERT INTO @Categories
SELECT Categories.CategorylD, Categories.CategoryName,
Products.ProductID, Products.ProductName,
SUM(CONVERT(MONEY,([Order Details].UnitPr1ce*Quantity*(1-Discount)/
100))*100)
AS ProductSales
FROM (Categories
INNER JOIN Products
OH Categories.CategoryID - Products.CategoryID)
INNER JOIN (Orders
INNER JOIN [Order Details]
OH Orders.OrderID - [Order Details].OrderID)
ON Products.ProductID - [Order Details].ProductID
WHERE (DATEPART(year, Orders.ShippedDate) - @year)
GROUP BY Categories.CategoryID, Categories.CategoryName,
Products.ProductID, Products.ProductName
RETURN
END
GO

В Листинге 2 показано, как вернуть значение табличной переменной. Явное определение таблицы как таковой позволяет осуществлять различные действия над этой таблицей (например, применять выражения типа insert) до того, как вернуть результат. Но есть, однако, особый тип пользовательских функций, возвращающих тип table, и называемых in-line функциями. Такие функции возвращают resultset единичного выражения select, без объявления табличных переменных и без определения таблицы в операторе RETURNS.

In-line функции подчиняются следующим правилам:

• Выражение RETURNS содержит только ключевое слово table.
• Тело функции должно состоять из единственного оператора RETURN, содержащего единичное выражение SELECT, заключенное в круглые скобки.

Вспомните хранимую процедуру БД Northwind, возвращающую 10 самых дорогих продуктов. Пример реализации того же в виде in-line функции может выглядеть так:

CREATE FUNCTION dbo.TenMostExpensiveProductsTable ()
RETURNS TABLE
AS
RETURN (SELECT TOP 10
ProductName AS TenMostExpensiveProducts,
UnitPrice, SupplierID, UnitsInStock, UnitsOnOrder
FROM Products
ORDER BY Products.UnitPrice DESC)

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

SELECT Products.*, Suppliers.*
FROM TenMostExpensiveProductsTable() AS Products
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

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