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

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

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

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

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

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

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

   SELECT

   INSERT

   UPDATE и DELETE

   CREATE TABLE

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

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

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

   Блокировки

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

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

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

   Роли

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

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

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

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

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

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

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

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

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

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

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&apos;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 поддерживаются следующие типы данных:

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

В версии 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.

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

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

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 уровни изоляции транзакций:

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

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 

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

Транзакция продолжается до тех пор, пока не будет выдана команда 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 ANSI_DEFAULTS устанавливает режим максимальной совместимости с ANSI SQL 92. При установке SET ANSI_DEFAULTS ON устанавливаются в ON следующие параметры:

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