Особенности работы с Microsoft SQL Server в Delphi

Источник: sqlru

Анатолий Тенцер

Обзор сервера

Microsoft SQL Server - прямой потомок Sybase SQL Server, с которым его до сих пор связывает много общего, в первую очередь язык программирования Transact-SQL (далее T-SQL). Однако в версии 7.0, как заявляет Microsoft, сервер переписан полностью и больше не содержит кода от Sybase. Главным достоинством Microsoft SQL Server является его тесная интеграция с Windows NT и семейством продуктов Back Office - общая модель защиты, базирующаяся на защите Windows NT, единая консоль администрирования (Microsoft Management Console), единый набор программных интерфейсов для доступа к данным (OLE DB). Текущая версия сервера на момент написания этой книги - Microsoft SQL Server 2000. Сервер выпускается в следующих редакциях:

Personal - версия, работающая под управлением Windows 95 и Windows NT Workstation. Предназначена в основном для отладки при разработке приложений. Поддерживает до двух процессоров. Реально можно работать (имея загруженным сервер, Query Analyzer и Enterprise Manager) на конфигурации Pentium-100/40 Мбайт под управлением Windows 95.
Standard - стандартная редакция. Работает под управлением Windows NT Server. Поддерживает до четырех процессоров.
Enterprise - расширенная редакция. Поддерживает до 32 процессоров, включает в себя некоторые дополнительные возможности.
Developer - редакция для разработчиков. По функциональности приблизительно соответствует Enterprise.

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

Таким образом, сервер работает на всей линейке операционных систем Microsoft на процессорах Intel и Alpha.

Нельзя не упомянуть и такой продукт, как Microsoft Data Engine (MSDE), - это версия Microsoft SQL Server без графических средств администрирования и с ограничениями по размеру базы данных (2 Гбайт) и количеству пользователей (5). MSDE предназначен для построения встраиваемых систем, которые при необходимости легко могут быть перенесены на полнофункциональную редакцию сервера, а также, например, использованы для создания демонстрационных версий продуктов. Администрирование MSDE может производиться при помощи утилиты osql, клиентских утилит от Microsoft SQL Server либо из Microsoft Access 2000. MSDE поставляется в составе Microsoft Office 2000 Professional и Microsoft Visual Studio.

Из дополнительных возможностей следует отметить:

OLAP-сервер - средство для создания и представления многомерных кубов данных, использующихся в аналитических системах. На сегодня Microsoft SQL Server является одним из самых дешевых, представленных на рынке решений подобного рода. OLAP Services входят в стандартную поставку и не требуют дополнительных отчислений.
Полнотекстовый поиск по базе данных. В сервер включена специальная версия Microsoft Index Server, с помощью которой можно построить по текстовым (включая BLOB) полям полнотекстовый индекс и расширения SQL, позволяющие строить запросы по этому индексу, например:

 SELECT ProductName
 FROM Products
 WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')

English Query - инструмент для формирования запросов на естественном английском языке, например: "How many blue Fords were sold in 1996?" В настоящее время существуют решения российских фирм, позволяющие выполнять полнотекстовый поиск на русском языке с учетом словоформ и формировать запросы на русском языке. Ознакомиться с ними можно по адресу: http://www.microsoft.com/rus/sql.
Data Transformation Services (DTS) - универсальный инструмент для перемещения данных между гетерогенными источниками. Единственное требование, предъявляемое к такому источнику данных, - наличие ODBC-драйвера, а лучше - OLE DB-провайдера. DTS позволяет создавать сложные сценарии переноса данных. Инструмент полезен не только при переносе данных на Microsoft SQL Server, но и для любой другой СУБД.
Встроенную в сервер поддержку XML, позволяющую использовать XML-документ в качестве источника данных в запросе и выдавать результаты запроса в виде XML-документа.

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

Все перечисленное делает Microsoft SQL Server привлекательным решением для реализации баз данных на платформе Windows NT.

Особенности реализации клиентской части

До версии 6.5 основным интерфейсом доступа к Microsoft SQL Server со стороны клиента была библиотека DB-Library. Она реализовывала набор низкоуровневых интерфейсов, позволяющих организовать взаимодействие с сервером. Однако в версии 7 был введен новый интерфейс доступа - OLE DB. В связи с этим развитие DB-Library было прекращено, и теперь библиотека служит лишь для обеспечения обратной совместимости. Доступ через нее не поддерживает новых расширений сервера (Unicode, текстовые поля до 8 Кбайт, тип данных GUID). Тем не менее старые приложения, не использующие этой функциональности, сохраняют работоспособность, однако в связи с изменениями в 7-й версии может понадобиться некоторая их переделка. Драйвер SQL Links реализует доступ при помощи DB-Library, поэтому воспользоваться этими расширениями с его помощью невозможно. Для обеспечения полноценного доступа к Microsoft SQL Server 7.0 и выше необходимо использовать в приложении новый набор компонентов ADOExpress, включенный в Delphi 5. Возможно также применение BDE, но при этом сервер доступен в объеме возможностей версии 6.х. Cуществует также ODBC-драйвер, посредством которого возможен полнофункциональный доступ к серверу. При работе с сервером версии 2000 на применение BDE накладываются дополнительные ограничения, связанные с использованием индексов по вычисляемым полям.

Доступ при помощи ADOExpress

ActiveX Data Objects (ADO) - надстройка над интерфейсом OLE DB, позволяющая обеспечить бизнес-приложениям высокоуровневый доступ к данным. Эта технология включена в Windows 2000, а для остальных версий Windows доступна в виде бесплатного обновления. ADO автоматически инсталлируется на компьютер при установке клиента Microsoft SQL Server.

Начиная с 5.0-й версии в Delphi (в редакции Enterprise) включен набор компонентов, позволяющих работать с ADO. Пользователи Delphi Professional могут приобрести эти компоненты в виде отдельного продукта.

В качестве драйверов баз данных ADO использует так называемые OLE DB-провайдеры, которые представляют собой COM-серверы, реализующие предопределенный набор COM-интерфейсов. Например, для доступа к набору данных служит интерфейс IRowset, возвращаемый OLE DB при открытии этого набора данных. Для того чтобы указать, какой провайдер и с какими параметрами должен использоваться ADO, предусмотрена так называемая строка подключения (Connection String), содержащаяся в свойстве ConnectionString компонентов ADOExpress.

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

Для подключения к Microsoft SQL Server необходимо указать тип провайдера "Microsoft OLE DB Provider for SQL Server" и в следующем окне заполнить информацию, необходимую для подключения:

После этого можно использовать компоненты ADO в качестве обычных наследников класса TDataSet.

Следует отметить, что технология ADO в значительной мере оптимизирована для использования совместно с Microsoft SQL Server 7.0. Полностью поддерживается модель работы Prepare-Execute, позволяющая эффективно кэшировать планы запросов, серверные курсоры (свойства CursorLocation и CursorType), возможность возврата запросом или хранимой процедурой нескольких наборов данных, прямой доступ к таблицам на сервере (без промежуточной генерации запроса). Например, задав в TADOQuery.SQL следующий код:

SELECT * FROM One
 SELECT * FROM Two

можно в приложении получить два набора данных:

ADOQuery1.Open;
 ADODataSet1.RecordSet := ADOQuery1.NextRecordSet;

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

В исходной версии Delphi 5 технология ADOExpress содержит ряд серьезных ошибок, которые делают работу при помощи вышеупомянутых компонентов практически невозможной. Поэтому настоятельно рекомендуется установка пакетов обновления Delphi и последнего обновления ADO (AePatch.exe), доступного с сайта http://community.borland.com из раздела Code Central.

Доступ посредством BDE

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

В составе BDE поставляется драйвер SQL Link для Microsoft SQL Server, использующий библиотеку DB-Library, что исключает полноценный доступ к возможностям сервера. При работе с помощью этого драйвера, например, нельзя использовать символьные данные в кодировке Unicode, а также некоторые системные функции. Если необходимо получить доступ к таким данным, в запросе необходимо явно преобразовать их в один из поддерживаемых типов данных, например:

SELECT CAST(USER_NAME() AS VARCHAR(255))

При работе с сервером при помощи BDE и драйвера SQL Link необходимо помнить следующее:

В зависимости от кодовой страницы сервера он может воспринимать дату в различных форматах. Например: ‘1 Apr 1999’ для английской и ‘1 Апр 1999’ для русской. DB-Library пытается распознать кодовые страницы клиента и сервера и преобразовать дату в нужный вид, что, однако, не всегда удается сделать правильно. Для исключения возникающей ошибки необходимо проверить язык, установленный на сервере по умолчанию (SET LANGUAGE) и в программе Client Network Utility на закладке "DB Library Options" отключить флажок "Use International Settings", если на нем установлен us_english или включить, если установлен русский. Другой способ избежать данной проблемы - передавать даты на сервер в виде текстовой строки вида ‘yyyymmdd’. При преобразовании 8-символьной строки в дату сервер всегда интерпретирует ее таким образом и исключает возможность ошибки. Сделать это можно так:
```
 Params[0].AsString := FormatDateTime(‘yyyymmdd’,
 DateTimeVariable); 
```
При выполнении запроса на выборку данных сервер предполагает, что клиентская программа проведет считывание всех запрошенных данных до посылки на сервер следующего оператора. Однако BDE загружает записи с сервера только по мере необходимости (например, открыв в TDBGrid большую таблицу, мы прочтем с сервера только записи, которые поместятся на экран в ее видимой части). Остальные записи считываются по мере необходимости, например при прокрутке таблицы. Если в такой ситуации попытаться выполнить еще один запрос, BDE принудительно выполнит FetchAll для всех открытых, но не считанных до конца запросов. Это может привести к неожиданной задержке выполнения приложения.
При использовании индексов по вычисляемым полям, появившимся в Microsoft SQL Server 2000, необходимо, чтобы для сессии были установлены параметры:

 ANSI_NULLS ON
 ANSI_PADDING ON
 ANSI_WARNINGS ON
 ARITHABORT ON
 CONCAT_NULL_YIELDS_NULL ON
 QUOTED_IDENTIFIER ON
 NUMERIC_ROUNDABORT OFF

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

Особенности реализации серверной части

Microsoft SQL Server имеет встроенный язык программирования Transact SQL, являющийся процедурным расширением стандарта ANSI SQL 92 entry level. T-SQL имеет полный набор средств для написания хранимых процедур и триггеров. Кроме того, реализованы некоторые расширения стандартного языка SQL, которые необходимо знать разработчику.

SELECT

В ранних версиях внешнее объединение таблиц задавалось выражением *= и =* в предложении WHERE. Этот синтаксис поддерживается, но не рекомендуется и в очередных версиях будет исключен. Начиная с версии 6.5 сервер поддерживает стандартный синтаксис {LEFT/RIGHT/FULL} [OUTER] JOIN.

При выполнении SELECT в таблицу (SELECT INTO) функция IDENTITY(data_type[, seed, increment]) позволяет создать в этой таблице автоинкрементное поле IDENTITY и заполнить его. Посредством этой функции и временных таблиц можно пронумеровать результаты запроса.

SELECT IDENTITY(INTEGER, 1, 1) AS Counter, Name
 INTO #Temp
 FROM MyTable
 ORDER BY Name
 
 SELECT * FROM #Temp

Начиная с версии 7.0 оператор SELECT имеет модификаторы TOP n [PERSENT] [WITH TIES], позволяющие вывести первые n записей или n процентов записей. Указав WITH TIES, можно заставить сервер включить в результат все записи с таким же значением сортируемого поля, как и у последней из n записей. Если SELECT не имеет фразы ORDER BY, то набор записей не обязательно будет одинаковым.

В качестве одной из таблиц в запросе можно использовать вложенный запрос:

SELECT A.Name, A.Population, B.AvgPop
 FROM City A INNER JOIN
 (SELECT Country, AVG(Population) AS AvgPop
 FROM City GROUP BY Country ) AS B
 ON A.Country = B.Country

Этот запрос для каждого города выведет его название, количество жителей, а также среднее количество жителей на город в той стране, где он находится.

Функции OPENQUERY и OPENROWSET позволяют использовать в качестве одной из таблиц в запросе выборку из любого OLE DB-совместимого источника данных.

В Microsoft SQL Server 2000 можно в запросе указать выражение FOR XML, в результате чего будет возвращена строка, содержащая XML-представление выборки. Например, запрос:

SELECT O.OrderID, O.CustomerID, O.OrderDate,
 O.ShipName, O.ShipAddress, O.ShipCity, O.ShipRegion,
 P.ProductName, OD.UnitPrice, OD.Quantity
 FROM Orders O
 INNER JOIN [Order Details] OD ON O.OrderId = OD.OrderId
 INNER JOIN Products P ON OD.ProductId = P.ProductId
 WHERE O.OrderId = '10248'
 FOR XML AUTO

вернет результат:

<O OrderID="10248"
 CustomerID="VINET"
 OrderDate="1996-07-04T00:00:00"
 ShipName="Vins et alcools Chevalier"
 ShipAddress="59 rue de l'Abbaye"
 ShipCity="Reims">
 <P ProductName="Queso Cabrales">
 <OD UnitPrice="14.0000" Quantity="12"/>
 </P>
 <P ProductName="Singaporean Hokkien Fried Mee">
 <OD UnitPrice="9.8000" Quantity="10"/>
 </P>
 <P ProductName="Mozzarella di Giovanni">
 <OD UnitPrice="34.8000" Quantity="5"/>
 </P>
 </O>

Предусмотрено как автоматическое форматирование XML-результатов запроса, так и задание способа форматирования программистом.

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

CREATE PROCEDURE XMLParam
 @Ids VARCHAR(8000)
 AS

 DECLARE @idoc int
 EXEC sp_xml_preparedocument @idoc OUTPUT, @Ids
 SELECT O.*
 FROM Orders O
 INNER JOIN OPENXML (@idoc, '/ROOT/Ids', 1) WITH (ID INT) AS T ON
 .OrderId = T.Id
 EXEC sp_xml_removedocument @idoc
 GO

Вызов этой процедуры выглядит следующим образом:

DECLARE @S VARCHAR(8000)
 
 SET @S = '<ROOT>
 <Ids ID="10250"/>
 <Ids ID="10257"/>
 <Ids ID="10258"/>
 </ROOT>'
 
 EXECUTE XMLParam @S

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

INSERT

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

INSERT author_sales EXECUTE get_author_sales

UPDATE и DELETE

Сервер поддерживает расширенный синтаксис

UPDATE MyTable

 SET Name = ‘Иванов’
 FROM MyTable T INNER JOIN AnotherTable A ON T.Id = A.MyTableId
 AND A.SomeField = 20

CREATE TABLE

В версии 7.0 поддерживаются следующие типы данных:

Тип	Синоним	Примечание
BIT		Целое число, равное 0 или 1. В Delphi возможно обращение к полю этого типа при помощи свойства AsBoolean (1 = True, 0 = False)
INT	INTEGER	32-битное целое число в диапазоне от - 2 147 483 648 до 2 147 483 647
SMALLINT		16-битное целое число в диапазоне от 32 768 до 32 767
TINYINT		8-битное целое число в диапазоне от 0 до 255
DECIMAL[(P[, S])]	NUMERIC, DEC	Десятичное число с фиксированной точностью в диапазоне от - 10³⁸ -1 до 10³⁸ - 1. P - максимальное количество знаков в числе S - количество знаков после запятой
MONEY		Денежный тип данных. Целое 64-битное число, младшие 4 разряда которого отведены под дробную часть. Может хранить числа в диапазоне от -922 337 203 685 477,5808 до 922 337 203 685 477,5807. В Delphi соответствует типу данных Currency
SMALLMONEY		Аналогичен Money, но 32-разрядный и ограничен диапазоном от -214 748,3648 до 214 748,3647
FLOAT	DOUBLE PRECISION	Число с плавающей точкой в диапазоне от -1.79E + 308 до 1.79E + 308
REAL		Число с плавающей точкой в диапазоне от -3.40E + 38 до 3.40E + 38
DATETIME		Дата и время в диапазоне от 1 января 1753 г. до 31 декабря 9999 г. с точностью 3.33 мс
SMALLDATETIME		Дата и время в диапазоне от 1 января 1900 г. до 6 июня 2079 г. с точностью до 1 мин
TIMESTAMP		Уникальный идентификатор в пределах базы данных. Этот тип данных НЕ СОДЕРЖИТ времени и лишь гарантирует, что поле этого типа уникально в рамках базы данных
UNIQUEIDENTIFIER		Глобальный уникальный идентификатор. Статистически уникальное 16-битное значение. Над этим типом данных определены лишь операции =, <>, IS NULL и IS NOT NULL
CHAR[(N)]	CHARACTER, VARYING VARCHAR	Строка фиксированной длины. N - длина строки. Максимальная длина - 8 тыс. символов
VARCHAR[(N)]	CHARACTER VARYING(N)	Строка переменной длины. N - длина строки. Максимальная длина - 8 тыс. символов
TEXT		Строка произвольной длины (до 2 147 483 647 символов)
NCHAR[(N)]	NATIONAL CHARACTER, NATIONAL CHAR	Строка фиксированной длины в формате Unicode. N - длина строки. Максимальная длина - 4 тыс. символов
NVARCHAR[(N)]	NATIONAL CHARACTER VARYING(N), NATIONAL CHAR VARYING(N)	Строка переменной длины в формате Unicode N - длина строки. Максимальная длина - 4000 символов
NTEXT	NATIONAL TEXT	Строка произвольной длины (до 1 073 741 823 символов)
BINARY[(N)]	VARYING VARBINARY	Двоичные данные фиксированной длины (до 8000 байт) N - длина данных
VARBINARY[(N)]		Двоичные данные переменной длины (до 8000 байт) N - длина данных
IMAGE		Двоичные данные произвольной длины (до 2 147 483 647 байт)

В версии SQL 2000 дополнительно предусмотрены следующие типы данных:

Тип	Синоним	Примечание
BIGINT		64-битное целое число
SQL_VARIANT		Может хранить данные произвольного типа

В версии 7.0 поддерживается создание вычисляемых полей

CREATE TABLE MyTable (
 Direction BIT NOT NULL,
 Amount MONEY,
 CASE Direction
 WHEN 1 THEN Amount
 ELSE -Amount
 END AS SignedAmount
 )

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

Написание триггеров

Триггеры в Microsoft SQL Server срабатывают после обновления и лишь один раз на оператор (а не на каждую обновленную запись). Количество триггеров на таблицу не ограничено. В триггере доступны обновленная таблица и две виртуальные таблицы Inserted и Deleted.

В них находятся:

	Inserted	Deleted
INSERT	Вставленные записи	Нет записей
UPDATE	Новые версии записей	Старые версии записей
DELETE	Нет записей	Удаленные записи

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

CREATE TRIGGER T1 ON MyTable FOR INSERT, UPDATE
 AS BEGIN
 -- Заносим в поля:
 -- LastUserName - имя пользователя, последним обновившего запись
 -- LastDateTime - дату и время последнего обновления
 UPDATE MyTable
 SET LastUserName = SUSER_NAME(),
 LastDateTime = GETDATE()
 FROM Inserted I INNER JOIN MyTable T ON I.Id = T.Id
 END

CREATE TRIGGER T2 ON MyTable FOR DELETE
 AS BEGIN
 -- Этот триггер откатывает и снимает всю транзакцию
 -- вызвавшую ошибку
 IF EXISTS (SELECT * FROM Deleted
 WHERE Position = ‘Boss’) BEGIN
 RAISERROR(‘Нельзя удалять начальника’, 16, 1)
 ROLLBACK
 END
 END

CREATE TRIGGER T3 ON MyTable FOR DELETE
 AS BEGIN
 -- А этот просто не дает удалить запись
 -- позволяя продолжить транзакцию
 IF EXISTS (SELECT * FROM Deleted
 WHERE Position = ‘Programmer’) BEGIN
 INSERT INTO MyTable
 SELECT * FROM Deleted
 WHERE Position = ‘Programmer’
 RAISERROR(‘Программиста удалить тоже не получится’, 16, 1)
 END
 END

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

CREATE TABLE Main (
 Id INTEGER PRIMARY KEY
 )
 
 CREATE TABLE Child (

 Id INTEGER PRIMARY KEY,

 MainId INTEGER NOT NULL REFERENCES Main(Id)
 )

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

CREATE PROCEDURE DeleteFromMain
 @Id INTEGER
 AS BEGIN
 DECLARE @Result INTEGER
 BEGIN TRANSACTION
 SAVE TRANSACTION DeleteFromMain
 DELETE Child WHERE MainId = @Id
 DELETE Main WHERE Id = @Id
 SET @Result = @@ERROR
 IF @Result <> 0
 ROLLBACK TRANSACTION DeleteFromMain
 COMMIT
 END

Другой способ - реализация ограничений ссылочной целостности только при помощи триггеров.

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

Пакеты команд

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

BEGIN TRANSACTION
 INSERT One (SomeField) VALUES (:1)
 INSERT Two (AnotherField) VALUES (:2)
 IF @@ERROR = 0
 COMMIT
 ELSE
 ROLLBACK

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

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

Разделителем пакетов команд служит оператор GO.

Обработка ошибок

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

выполнение процедуры этой функцией не прерывается, транзакции не откатываются. Если в этом есть необходимость, используйте ROLLBACK или RETURN;
ошибки с параметром severity ниже 10 являются информационными и не вызывают исключения компонентов работы с данными.

При возникновении ошибки в каком-либо из операторов внутри пакета выполнение пакета продолжается, а функция @@ERROR возвращает код ошибки, который можно обработать.

INSERT MyTable (Name) VALUES (‘Петров’)
 IF @@ERROR != 0
 PRINT ‘Ошибка вставки’.

После успешного оператора @@ERROR возвращает 0, поэтому если значение ошибки может понадобиться впоследствии, то его необходимо сохранить в переменной.

DECLARE @ErrCode INTEGER
 
 SET @ErrCode = 0
 
 BEGIN TRANSACTION
 INSERT MyTable (Name) VALUES (‘Иванов’)
 IF @@ERROR != 0
 @ErrCode = @@ERROR
 
 INSERT MyTable (Name) VALUES (‘Петров’)
 IF @@ERROR != 0
 @ErrCode = @@ERROR
 
 IF @ErrCode = 0
 COMMIT
 ELSE BEGIN
 ROLLBACK
 RAISERROR(‘Не удалось обновить данные’, 16, 1)
 END

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

UPDATE MyTable
 SET Name = ‘Сидоров’
 WHERE Name = ‘Петров’
 
 IF @@ROWCOUNT = 0
 PRINT ‘Петров не найден’

Блокировки

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

Другой важной проблемой является модель обеспечения уровней изоляции транзакций REPEATABLE READ и SERIAZABLE. При выполнении транзакции с этим уровнем изоляции сервер блокирует диапазоны значений полей, по которым осуществляется выборка данных для предотвращения вставки "фантомных" значений. Например, если в транзакции с уровнем изоляции SERIAZABLE будет выполнен запрос

SELECT * FROM MyTable WHERE Name BETWEEN ‘A’ AND ‘C’

то сервер наложит блокировку по записи (Shared Lock) на диапазон значений, попавших в результат запроса, предотвращая тем самым вставку "фантомных" записей другими транзакциями. Блокировка будет удерживаться до конца транзакции. На измененные транзакцией записи накладывается блокировка по чтению (Exclusive Lock), предотвращающая чтение их другими транзакциями. Поэтому транзакции с высокими уровнями изоляции необходимо тщательно планировать и делать их максимально короткими.

Обработка транзакций

В Microsoft SQL Server поддерживаются все определенные стандартом ANSI SQL 92 уровни изоляции транзакций:

READ UNCOMMITTED	Позволяет транзакции читать неподтвержденные данные других транзакций
READ COMMITTED	Предотвращает считывание транзакцией данных, не подтвержденных другой транзакцией
REPEATABLE READ	Все блокировки удерживаются до конца транзакции, гарантируя идентичность повторно считанных данных прочитанным ранее
SERIALIZABLE	Гарантирует отсутствие "фантомов". Реализуется за счет блокирования диапазонов записей, внутри которых эти "фантомы" могут появиться

Для установки текущего уровня изоляции используется оператор

SET TRANSACTION ISOLATION LEVEL
 {
 READ COMMITTED
 / READ UNCOMMITTED
 / REPEATABLE READ
 / SERIALIZABLE
 }

Момент начала транзакции регулируется установкой

SET IMPLICIT_TRANSACTION ON/OFF

По умолчанию она установлена в ON, и каждый оператор выполняется в отдельной транзакции. По его завершении неявно выполняется COMMIT. Если необходимо выполнить транзакцию, состоящую из нескольких операторов, ее надо явно начать командой BEGIN TRANSACTION. Заканчивается транзакция оператором COMMIT или ROLLBACK.

Например:

INSERT MyTable VALUES (1)

-- Выполнился внутри отдельной транзакции
 BEGIN TRANSACTION
 -- Начали явную транзакцию
 INSERT MyTable VALUES (2)
 INSERT MyTable VALUES (3)
 COMMIT
 -- завершили явную транзакцию

При выдаче команды

SET IMPLICIT_TRANSACTION OFF

сервер начинает новую транзакцию, если она еще не начата и выполнился один из следующих операторов:

ALTER TABLE	FETCH	REVOKE
CREATE	GRANT	SELECT
DELETE	INSERT	TRUNCATE TABLE
DROP	OPEN	UPDATE

Транзакция продолжается до тех пор, пока не будет выдана команда COMMIT или ROLLBACK.

Возможно создание вложенных транзакций. При этом функция @@TRANCOUNT показывает глубину вложенности транзакции. Например:

BEGIN TRANSACTION
 SELECT @@TRANCOUNT -- Выдаст 1
 BEGIN TRANSACTION
 SELECT @@TRANCOUNT -- Выдаст 2
 COMMIT
 SELECT @@TRANCOUNT -- Выдаст 1
 COMMIT
 SELECT @@TRANCOUNT -- Выдаст 0

Вложенный BEGIN TRANSACTION не начинает новую транзакцию. Он лишь увеличивает @@TRANCOUNT на единицу. Аналогично вложенный оператор COMMIT не завершает транзакцию, а лишь уменьшает @@TRANCOUNT на единицу. Реальное завершение транзакции происходит, когда @@TRANCOUNT становится равным нулю. Такой механизм позволяет писать хранимые процедуры, содержащие транзакцию, например:

 CREATE PROCEDURE Foo
 AS BEGIN
 BEGIN TRANSACTION
 INSERT MyTable VALUES (1)
 INSERT MyTable VALUES (1)
 COMMIT
 END

При запуске вне контекста транзакции процедура выполнит свою транзакцию. Если она запущена внутри транзакции, внутренние BEGIN TRANSACTION и COMMIT просто увеличат и уменьшат счетчик транзакций.

Оператор ROLLBACK ведет себя по-иному. Он всегда, независимо от текущего уровня вложенности, устанавливает значение переменной @@TRANCOUNT равным нулю и отменяет все изменения, от начала самой внешней транзакции. Если в хранимой процедуре возможен откат ее действий исходя из какого-то условия, можно использовать точки сохранения (savepoint)

CREATE PROCEDURE Foo
 AS BEGIN
 BEGIN TRANSACTION
 -- Этот оператор не может быть отменен вне контекста
 -- основной транзакции
 INSERT MyTable VALUES (1)
 SAVE TRANSACTION InsideFoo
 -- Операторы, начиная отсюда, могут быть отменены
 -- без отката основной транзакции
 INSERT MyTable VALUES (2)
 INSERT MyTable VALUES (3)
 IF (SELECT COUNT(*) FROM MyTable) > 3
 ROLLBACK TRANSACTION InsideFoo
 -- Отменяем изменения, внесенные после
 -- последнего savepoint
 COMMIT
 END

Отдельного обсуждения заслуживает команда ROLLBACK, вызванная в триггере.

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

CREATE TABLE MyTable (Id INTEGER)
 
 GO
 
 CREATE TRIGGER MyTrig ON MyTable FOR INSERT
 AS BEGIN

 IF (SELECT MAX(Id) FROM Inserted) >= 2 BEGIN
 ROLLBACK
 RAISERROR(‘Id >= 2’, 17, 1)
 END
 END
 
 GO
 
 INSERT MyTable VALUES (1)
 INSERT MyTable VALUES (2) - Вызовет ROLLBACK в триггере
 -- Операторы, начиная отсюда, не выполнятся
 INSERT MyTable VALUES (3)
 INSERT MyTable VALUES (4)

Соответствие стандарту ANSI SQL 92

В Microsoft SQL Server имеются настройки, позволяющие изменять степень соответствия сервера стандарту ANSI SQL 92.

SET ANSI_NULLS {ON/OFF} - регулирует результат сравнения значений, содержащих NULL. Если ANSI_NULLS = OFF, то запрос

SELECT * FROM MyTable WHERE MyField = NULL

вернет все строки, в которых MyField установлено в NULL. Если ANSI_NULLS = OFF, то в соответствии со стандартом ANSI SQL92 сравнение с NULL возвращает UNKNOWN. Другие установки, на которые следует обратить внимание:

SET CURSOR_CLOSE_ON_COMMIT	Устанавливает режим закрытия курсоров по завершении транзакции
SET ANSI_NULL_DFLT_ON и SET ANSI_NULL_DFLT_OFF	Устанавливают возможность принятия значения NULL полем по умолчанию при создании таблицы
SET IMPLICIT_TRANSACTIONS	Устанавливает режим Autocommit
SET ANSI_PADDING	Устанавливает режим "отсечения" концевых пробелов для вновь создаваемых полей
SET QUOTED_IDENTIFIER	Разрешает выделение идентификаторов двойными кавычками
SET ANSI_WARNINGS	Устанавливает реакцию на математические ошибки

Рассмотрение этих параметров выходит за рамки данной статьи, однако при чтении документации необходимо обратить на них внимание.

Параметр SET ANSI_DEFAULTS устанавливает режим максимальной совместимости с ANSI SQL 92. При установке SET ANSI_DEFAULTS ON устанавливаются в ON следующие параметры:

SET ANSI_NULLS	SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_NULL_DFLT_ON	SET IMPLICIT_TRANSACTIONS
SET ANSI_PADDING	SET QUOTED_IDENTIFIER
SET ANSI_WARNINGS

По умолчанию ANSI_DEFAULTS = ON для клиентов ODBC и OLE DB (ADO) и OFF для клиента DB-Library (BDE). Поскольку предпочтительным (и поддерживаемым в будущем) методом доступа является OLE DB, то при разработке клиентской части, использующей BDE, рекомендуется явно устанавливать SET ANSI_DEFAULTS ON. С различиями в значении этого параметра связана и проблема, возникающая при разработке запросов посредством Query Analyzer. Если в нем и в клиентском приложении имеются разные настройки совместимости с ANSI, одни и те же запросы могут выдавать разные результаты. Поэтому рекомендуется проверять настройки Query Analyzer на предмет их соответствия тем, которые предполагаются в клиентском приложении.

Модель безопасности

Роли

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

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

Интегрированная безопасность

Сильной стороной Microsoft SQL Server является его тесная интеграция с системой безопасности Windows NT. Права на доступ к серверу и базам данных можно давать пользователям и группам Windows NT. Механизм делегирования полномочий позволяет пользователям, подключившимся к одному из серверов, иметь доступ к другим серверам в сети со своими правами, отличающимися от прав сервера, к которому они подключились. Также возможна прозрачная для пользователя проверка его полномочий при доступе к серверу через Microsoft Internet Information Server или Microsoft Transaction Server.

Оптимизатор запросов Microsoft SQL Server

В версии 7.0 существенно переработан оптимизатор запросов. Сервер может использовать несколько индексов на каждую таблицу в запросе; один запрос может исполняться параллельно на нескольких процессорах. В SQL Server 7.0 реализованы три метода выполнения операции слияния таблиц (JOIN):

LOOP JOIN - для каждой записи в одной из таблиц производится цикл по связанным записям второй таблицы. Этот метод наиболее эффективен для малых результирующих наборов данных.
MERGE JOIN - требует, чтобы оба набора данных были отсортированы по сливаемому полю (набору полей). В этом случае сервер осуществляет слияние за один проход по каждому из наборов данных. Поскольку они уже упорядочены, нет необходимости просматривать все записи, достаточно выбирать их начиная с текущей, пока значение поля не изменится. Это самый быстрый метод слияния больших наборов данных.
HASH JOIN используется, когда невозможно использовать MERGE JOIN, а наборы данных велики. По одному из наборов строится хэш-таблица, а затем для каждой записи из второго набора вычисляется та же хэш-функция и производится ее поиск в таблице. В случае больших неотсортированных наборов данных этот алгоритм существенно эффективнее, чем LOOP JOIN.

При фильтрации по индексу сервер не осуществляет сразу выборку данных из таблицы. Вместо этого строится набор "закладок" (Bookmark), а затем производится выборка данных в одной операции (Bookmark Lookup). Это позволяет резко снизить количество обращений к диску.

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

CREATE TABLE T1 (
 Id INTEGER PRIMARY KEY,
 ...
 )
 
 CREATE TABLE T2 (
 Id INTEGER PRIMARY KEY,
 ...
 )
 
 CREATE TABLE T3 (
 Id INTEGER PRIMARY KEY,
 T1Id INTEGER REFERENCES T1(Id),
 T2Id INTEGER REFERENCES T2(Id),
 ...
 )

запрос

SELECT *
 FROM T1
 INNER JOIN T3 ON T1.Id = T3.T1Id
 INNER JOIN T2 ON T2.Id = T3.T2Id
 WHERE ...

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

CREATE INDEX T3_1 ON T3(T1Id, T2Id)

После слияния T3 с T1 он позволяет получить упорядоченный по T2Id набор данных, который может быть слит с T2 путем эффективного алгоритма MERGE JOIN. Впрочем, лучший эффект, возможно, даст индекс:

CREATE INDEX T3_2 ON T3(T2Id, T1Id)

Это зависит от количества записей в T1, T2 и распределения их сочетаний в T3. В OLAP-системе (или в слабо загруженном OLTP-приложении) лучше построить оба этих индекса, в то время как при интенсивном обновлении таблицы T3, возможно, от одного из них придется отказаться. Сервер может сам выдать рекомендации по построению индексов - для этого в него включен Index Tuning Wizard, доступный через Query Analyzer. Он анализирует запрос (или поток команд, собранный при помощи SQL Trace) и выдает рекомендации по структуре индексов в конкретной базе данных.

В процессе работы с Microsoft SQL Server в оптимизаторе запросов автором были обнаружены два "тонких" места , которые рекомендуется учитывать.

Алгоритм выбора способа объединения таблиц не всегда выдает оптимальный результат. Это обычно бывает связано с невозможностью определить точное количество записей, участвующих в объединении на момент генерации плана запроса.
```
DECLARE @I INTEGER
 
 SET @I = 10
 
 SELECT *
 FROM History H
 INNER JOIN Objects O ON O.Id = H.ObjectId
 WHERE H.StatusId = @I 
```
Сервер сгенерировал следующий план исполнения:

Внимание: в качестве параметра выступает переменная, при этом сервер не может точно оценить, в какой диапазон статистики она попадет. В этом случае он делает предположение, что количество записей, полученных из History, будет равно средней селективности по используемому полю, помноженной на количество записей в таблице (в данном случае - 10 151). Исходя из этого выбирается алгоритм слияния HASH JOIN, требующий значительных накладных расходов на построение хэш-таблицы. В случае если реальное количество записей ощутимо меньше (реально этот запрос выбирает 100-200 записей, имеющих соответствующий StatusId за последний день), алгоритм LOOP JOIN дает во много раз большую производительность. Итак, если вы точно знаете, что фильтрация по конкретному полю даст ограниченный набор данных (не более нескольких сотен записей), а сервер об этом "не догадывается", - укажите ему алгоритм слияния явно.
```
SELECT *
 FROM History H
 INNER LOOP JOIN Objects O ON O.Id = H.ObjectId
 WHERE H.StatusId = @I
```
Делать это надо, только если вы уверены, что этот запрос будет выполняться со значениями параметра, имеющими высокую селективность. На больших наборах данных выполнение LOOP JOIN будет гораздо медленнее.
Цена операции Bookmark Lookup (извлечение данных из таблицы по известным значениям индекса) явно завышена. Поэтому иногда, даже при наличии подходящего индекса, вместо INDEX SCAN (поиск по индексу) с последующим Bookmark Lookup (выборка из таблицы) сервер принимает решение о полном сканировании таблицы (TABLE SCAN или CLUSTERED INDEX SCAN). Пример такого запроса приведен на рисунке. Обратите внимание на предполагаемую стоимость запроса (Estimated subtree cost) для случая, когда для таблицы явно задан поиск по индексу: она чрезвычайно завышена. Видно, что 100% расчетной стоимости выполнения дает операция Bookmark Lookup. Реально же этот запрос быстрее выполняется при индексном доступе, чем при сканировании таблицы. В этом случае рекомендуется попробовать явно указать индекс для доступа к таблице.

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

Другие особенности Microsoft SQL Server

Получение уникальных идентификаторов

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

Для получения целочисленного уникального идентификатора записи в Microsoft SQL используется ключевое слово IDENTITY [(seed, increment)].

Здесь:

seed - начальное значение

increment - приращение

По умолчанию seed и increment равны 1.

Чтобы создать в таблице автоинкрементный столбец, необходимо записать:

 CREATE TABLE TableName (
 Id INTEGER NOT NULL IDENTITY
 )

Иметь атрибут IDENTITY в таблице может только одна из колонок: TINYINT, SMALLINT, INT или DECIMAL(p,0). После этого при вставке новых записей поле Id будет получать новое значение счетчика. Если таблица имеет поле с установленным IDENTITY, то к этому полю можно обратиться при помощи ключевого слова IDENTITYCOL. Например, запрос

 SELECT IDENTITYCOL FROM TableName

эквивалентен

 SELECT Id FROM TableName

если поле Id создано с атрибутом IDENTITY.

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

DECLARE @IdentityValue INTEGER
 
 INSERT MainTable (Name) VALUES (‘Петров’)
 
 SELECT @IdentityValue = @@IDENTITY
 
 INSERT ChildTable (MainId, Data) VALUES (@IdentityValue, ‘Первая’)
 INSERT ChildTable (MainId, Data) VALUES (@IdentityValue, ‘Вторая’)

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

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

INSERT MainTable (Name) VALUES (‘Петров’)

функция @@IDENTITY уже не вернет значения для MainTable.

В версии Microsoft SQL Server 2000 появилась функция SCOPE_IDENTITY(),аналогичная @@IDENTITY, однако возвращающая значение, вставленное в текущем контексте (триггере, хранимой процедуре, пакете команд). Например, в предыдущем примере SCOPE_IDENTITY() вернет значение, вставленное в MainTable, независимо от операций в триггере, поскольку они выполняются уже не в текущем контексте.

Значения seed и increment можно использовать, например, для предоставления диапазонов значений первичного ключа в распределенной базе данных. Например, один филиал может генерировать значения, начиная с 1, другой - с 1 000 000 и т.д.

По умолчанию в поле с IDENTITY не может быть вставлено явное значение. Однако Microsoft SQL Server позволяет разрешить такую вставку путем установки

 SET IDENTITY_INSERT [database.[owner.]]{table} {ON/OFF}

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

Другим способом генерации уникальных идентификаторов, появившемся в Microsoft SQL 7.0, является тип данных UNIQUEIDENTIFIER. Физически это 16-байтовое число. Этот тип аналогичен GUID (Global Unique Identifier), активно использующемуся в технологии COM. Над этим типом данных определены только операции =, <>, IS NULL и IS NOT NULL. Сравнение >, < или т.п. не допускается. Для генерации значений используется функция NEWID()

CREATE TABLE MyUniqueTable (
 UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
 Characters VARCHAR(10)
 )
 
 GO
 
 INSERT INTO MyUniqueTable(Characters) VALUES ('abc')
 INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')

Приведенные операторы вставки эквивалентны, и оба создают записи с уникальными значениями UniqueColumn. Аналогично, значение может быть предоставлено клиентским приложением посредством функции CoCreateGUID при помощи свойства AsGUID классов TField и TParam, без опасения, что оно окажется неуникальным.

Временные таблицы

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

Для примера рассмотрим хранимую процедуру, которая выдает значения продаж по месяцам. Если в данном месяце продаж не было, выводится имя месяца и NULL.

CREATE PROCEDURE AmountsByMonths
 AS BEGIN
 DECLARE @I INTEGER
 CREATE TABLE #Months (
 Id INTEGER,
 Name CHAR(20)
 )
 SET @I = 1
 WHILE (@I <= 12) BEGIN
 INSERT Months (Id, Name) VALUES
 (@I, DATENAME(month, '1998' + REPLACE(STR(@I,2),' ','0')+'01'))
 SET @I = @I + 1
 END
 SELECT M.Name, SUM(P.Amount)
 FROM #Months M INNER JOIN Payment P
 ON M.Id = DATEPART(month, P.Date)
 DROP TABLE #Months
 END

В версии Microsoft SQL 2000 появилась возможность создавать переменные типа table, представляющие собой таблицу. Работа с такой переменной может выглядеть следующим образом:

DECLARE @T TABLE (Id INT)
 
 INSERT @T (Id) VALUES (10250)
 INSERT @T (Id) VALUES (10257)
 INSERT @T (Id) VALUES (10259)
 
 SELECT O.*
 FROM Orders O
 INNER JOIN @T AS T ON O.OrderId = T.Id

Переменные типа table более предпочтительны для использования, чем временные таблицы, поскольку последние приводят к невозможности кэширования плана запроса (он генерируется при каждом выполнении), а в случае использования переменных этого не происходит.

Создание хранимых процедур и триггеров

Если логика работы процедуры или триггера требует установки каких-либо SET-параметров в определенные значения, процедура или триггер могут установить их внутри своего кода. По завершении их выполнения будут восстановлены исходные параметры, которые были на сервере до запуска процедуры или оператора, вызвавшего срабатывание триггера. Исключением являются SET QUOTED_IDENTIFIER и SET ANSI_NULLS для хранимых процедур. Сервер запоминает их на момент создания процедуры и автоматически восстанавливает при исполнении.

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

Кластерные индексы

Microsoft SQL Server позволяет иметь в таблице один кластерный (CLUSTERED) индекс. Данные в таблице физически расположены на нижнем уровне B-дерева этого индекса, поэтому доступ по нему является самым быстрым. По умолчанию такой индекс автоматически создается по полю, объявленному первичным ключом. Все остальные индексы в качестве ссылки на запись хранят значение кластерного индекса этой записи, поэтому не рекомендуется строить его по полям большого размера. Кроме того, для оптимизации операции вставки записей рекомендуется строить этот индекс по полю с монотонно возрастающими значениями. Исходя из этих рекомендаций лучший кандидат на построение кластерного индекса - поле INTEGER (минимальный размер), IDENTITY (возрастание), объявленное как первичный ключ (заведомо уникальное, частый доступ по этому индексу).

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

В версии MicrosoftSQL 2000 предусмотрена возможность создавать функции в базе данных. Функции могут быть трех типов:

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

CREATE FUNCTION FirstWord (@S VARCHAR(255))
 RETURNS VARCHAR(255)

AS
 BEGIN
 DECLARE @I INT
 SET @I = CHARINDEX(' ', @S)
 RETURN CASE @I WHEN 0 THEN @S
 ELSE LEFT(@S, @I-1)
 END
 END
 GO
 
 SELECT dbo.FirstWord ('Hello world !')

Inline-табличные функции - состоят из одного оператора SELECT и возвращают его результат в виде таблицы

CREATE FUNCTION OrdersByCustomer (@S VARCHAR(255))
 RETURNS TABLE
 AS
 RETURN SELECT * FROM Orders WHERE CustomerId = @S
 GO
 
 SELECT *
 FROM OrdersByCustomer('VINET') AS T
 INNER JOIN [Order Details] OD ON OD.OrderId = T.OrderId

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

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

CREATE FUNCTION Months (@Quoter INT)
 RETURNS @table_var TABLE
 (Id int,
 Name VARCHAR(20))
 AS
 BEGIN
 DECLARE @Start INTEGER, @End INTEGER
 
 SET @Start = CASE
 WHEN @Quoter = 2 THEN 4
 WHEN @Quoter = 3 THEN 7
 WHEN @Quoter = 4 THEN 10
 ELSE 1
 END
 
 SET @End = CASE
 WHEN @Quoter = 1 THEN 3
 WHEN @Quoter = 2 THEN 6
 WHEN @Quoter = 3 THEN 9
 ELSE 12
 END
 
 WHILE (@Start <= @End) BEGIN
 INSERT @table_var (Id, Name) VALUES
 (@Start, DATENAME(month, '1998' + REPLACE(STR(@Start,2),' ','0')+'01'))
 SET @Start = @Start + 1
 END
 
 RETURN
 END
 GO

SELECT T.Name, SUM(O.Freight)
 FROM dbo.Months(NULL) AS T
 INNER JOIN Orders O ON DATEPART(month, O.OrderDate) = T.Id
 GROUP BY T.Name

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

Советы по работе с Microsoft SQL Server

Устанавливайте SET NOCOUNT ON. При установке OFF после каждого оператора сервер посылает клиенту сообщение DONE_IN_PROC с количеством обработанных записей. Запретив это, вы кардинально сократите сетевой трафик и существенно увеличите производительность.
При необходимости изменить режим блокирования какой-либо таблицы без изменения уровня изоляции транзакций используйте подсказки режима блокирования. Так, в приведенном ниже примере оператор SELECT накладывает блокировку на запись, предотвращая изменение ее другими сессиями до окончания транзакции, несмотря на то что установленный уровень изоляции не предусматривает этого:
```
SET TRANSACTION ISOLATION LEVEL READ COMMITED
 
 BEGIN TRANSACTION
 SELECT * FROM City
 WHERE Name = ‘Ленинград’
 WITH (HOLDLOCK)
 -- Какие-то операторы, во время их выполнения
 -- запись остается заблокированной
 UPDATE City
 SET Name = ‘Санкт-Петербург’
 WHERE Name = ‘Ленинград’
 COMMIT
```
Явно указывайте параметры в запросах. Это поможет серверу более эффективно кэшировать планы выполнения. При динамическом формировании текста запроса не подставляйте параметры в текст, а используйте хранимую процедуру sp_executesql.
Не начинайте названия своих хранимых процедур с префикса "sp_". Сервер ищет процедуры с такими именами в первую очередь в базе данных Master, а затем уже в текущей базе данных. Это приводит к дополнительным накладным расходам.
Если ваше приложение интенсивно использует базу данных tempdb (например, создает много временных таблиц), увеличьте ее минимальный размер. В противном случае при старте сервера создается tempdb малого размера, которая затем динамически расширяется, непроизводительно загружая компьютер.

КомпьютерПресс 6'2001