Предыдущая часть

Особенности работы с Microsoft SQL Server в Delphi 5
(Часть 4)

У Анатолий Тенцер
Статья была опубликована в "КомпьютерПресс", №6-2001

• Другие особенности Microsoft SQL Server
  • Получение уникальных идентификаторов
  • Временные таблицы
  • Создание хранимых процедур и триггеров
  • Кластерные индексы
  • Определяемые пользователем функции
  • Советы по работе с Microsoft SQL Server

Другие особенности 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 предусмотрена возможность создавать функции в базе данных. Функции могут быть трех типов:

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

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 !')

2. 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

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

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

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 малого размера, которая затем динамически расширяется, непроизводительно загружая компьютер.

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

• Подробнее о продуктах Borland
• Подробнее о Microsoft SQL Server
• Курсы по продуктам фирмы Borland
• Курсы по продуктам Microsoft
• Приобрести MS SQL Server в Интернет-магазине ITshop.ru
• Приобрести продукцию Borland в электронном магазине ITshop.ru

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Borland
Отправить ссылку на страницу по e-mail