Создайте локальную временную таблицу

Урок: Программные объекты

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

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

Вы научитесь:

• создавать локальные временные таблицы;
• создавать глобальные временные таблицы;
• создавать локальные переменные;
• использовать глобальные переменные;
• использовать команду SET для установки значения переменной;
• использовать команду SELECT для установки значения переменной.

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

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

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

Понятие о временных таблицах

Временные таблицы создаются такими же командами, с помощью которых Transact-SQL создает обычную таблицу – это CREATE или SELECT INTO. Различие состоит лишь в том, что имя временной таблицы начинается либо с #, либо с ##. Таким образом, первый оператор в представленном ниже листинге создает обычную таблицу, а второй – временную таблицу.

Единственное обстоятельство, которое следует учитывать при создании временной таблицы, состоит в том, что она не может иметь ограничений внешнего ключа FOREIGN KEY. Все другие возможности оператора CREATE TABLE доступны – временные таблицы могут иметь проверочное ограничение CHECK, значение по умолчанию и любую необходимую вам структуру.

Временные таблицы всегда создаются в системной базе данных tempdb. Поскольку tempdb всегда заново создается при запуске SQL Server, временные таблицы не могут сохраняться между сеансами. Кроме того, временные таблицы всегда уничтожаются при выходе за пределы их действия (область видимости), то есть тогда, когда никто их не использует.

Область действия временной таблицы определяется ее именем. Временная таблица, имя которой содержится один значок решетки, например #MyTable, имеет локальную (local) область действия. Временная таблица, содержащая в имени два значка, например ##MyTable, имеет глобальную (global) область действия.

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

Глобальные временные таблицы могут явно уничтожаться, или же SQL Server будет уничтожать их после того, как создавшее соединение закроется, и все текущее использование таблицы будет завершено. Например, если пользователь А создаст временную таблицу ##MyTable, пользователь В может выполнить команды через нее. Если пользователь А выйдет из системы, в то время как пользователь В использует таблицу ##MyTable, то любые исполняемые на данный момент команды SQL Server будут выполнены, но пользователь В не сможет больше исполнить каких-либо команд с помощью этой таблицы.

Использование временных таблиц

Временные таблицы создаются с помощью таких же команд, как и обычные таблицы: CREATE или SELECT INTO. Предоставляемая таблица является доступной для соединения (т. е., она не считается локальной таблицей, созданной другим соединением), и она может использоваться точно так же, как и обычная таблица.

Создайте локальную временную таблицу

1. В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос) для открытия нового окна Query (Запрос).
2. В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий). Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

1. Выберите сценарий CreateLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

1. В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос). Query Analyzer создаст временную таблицу.
2. В панели Object Browser выберите папку User Tables в базе данных tempdb.

Нажмите клавишу F5 для обновления содержимого окна Object Browser и откройте папку User Tables. Query Analyzer отобразит в списке таблицу dbo.#LocalTable.