Введение
Временные таблицы всегда прекрасно помогали разработчикам. Раньше, когда я использовал Access, я обычно создавал временные таблицы, которые удалял после решения задачи. При использовании SQL Server решить задачу можно гораздо проще. Не так ли?
Типы временных таблиц
Существует два типа временных таблиц. Это Локальные и Глобальные временные таблицы. Приведу пример для облегчения понимания временных таблиц. Следующий пример взят из Books on Line Microsoft SQL Server 2000.
"Два типа временных таблиц, локальные и глобальные, отличаются друг от друга своими именами, своей видимостью и своей доступностью. Локальные временные таблицы имеют префикс в имени в виде символа номера (#); они видны только в текущем соединении пользователя и удаляются после того, как пользователь отсоединится от экземпляра (instance) Microsoft SQL Server 2000. Глобальные временные таблицы имеют префикс имени в виде двойного символа #; они видны для любого пользователя после их создания и удаляются тогда, когда все пользователи, ссылающиеся на таблицу, отсоединятся от SQL Server."1
"Например, если вы создаете таблицу с названием employees, то эта таблица может быть использована любым пользователем, который имеет разрешения в базе данных на доступ к ней до тех пор, пока она не будет удалена. Если вы создаете локальную временную таблицу с именем #employees, то только вы являетесь субъектом, который может работать с этой таблицей, которая будет удалена, как только вы отключитесь. Если вы создаете глобальную временную таблицу ##employees, любой пользователь в базе данных может работать с этой таблицей. Если никто другой не работает с этой таблицей после того, как вы ее создали, то таблица удаляется после того, как вы отсоединитесь. Если другой пользователь работает с этой таблицей после того, как вы ее создали, то таблица удаляется после того, как вы оба отсоединитесь."2
Примечание: 1,2 выше взяты из Books on Line Microsoft SQL Server 2000 в разделе "Creating and Modifying"
В отличие от использования Access, вам нет необходимости удалять временные таблицы вручную, вместо этого вы можете положиться на SQL Server, который сделает это автоматически.
Использование временных таблиц
Временные таблицы используются для разных целей. Наиболее общее применение - это сохранение результатов вызова хранимой процедуры, уменьшение числа строк при соединениях, агрегирование данных из различных источников или замена курсоров и параметризированных представлений. Использование курсоров SQL Server влечет за собой большие накладные расходы. Обслуживание кода становится более простым при использовании временных таблиц в T-SQL. Значительно проще отлаживать хранимую процедуру, когда вы используете временные таблицы для хранения временных данных.
Альтернативы временным таблицам
Существует несколько альтернатив временным таблицам. Одна из них - использование производных таблиц. В SQL Server 2000 появился новый тип данных - "table", обладающий свойствами, сходными с временными таблицами. Его основное назначение - временное хранилище набора строк. "table" играет роль локальной переменной. "table" создается в памяти в отличие от временной таблицы, которая создается в базе данных tempdb, что, очевидно, значительно быстрее. Другой факт заключается в том, что "table" использует ограниченные ресурсы по сравнению с временными таблицами.
Ограничения временных таблиц
Временные таблицы создаются в базе данных tempdb и создают дополнительную нагрузку на SQL Server, снижая общую производительность. SQL Server испытывает большие проблемы при работе с временными таблицами.
Эффективное использование временных таблиц
Если у вас нет других вариантов, кроме как использовать временные таблицы, то делайте это эффективно. Вот несколько приемов для этого:
- Включайте только необходимые столбцы и строки вместо использования всех тех столбцов и данных, которые не имеют смысла для данной временной таблицы. Всегда фильтруйте ваши данные, помещая их во временные таблицы.
- При создании временных таблиц, не используйте операторы SELECT INTO. Вместо этого создавайте таблицу с помощью оператора DDL, а затем наполняйте ее данными, используя INSERT INTO.
- Используйте индексы на временных таблицах. Раньше я всегда забывал использовать индекс в тагоко рода таблицах. В особенности для больших таблиц продумывайте кластерные и некластерные индексы.
- После использования временной таблицы удаляйте ее. Это освободит ресурсы tempdb. Да, я согласен, что временные таблицы удаляются при закрытии соединения, но не дожидайтесь этого.
- Когда вы создаете временную таблицу, не делайте это в транзакции. Если вы будете создавать их внутри транзакции, это приведет к блокированию некоторых системных таблиц (syscolumns, sysindexes, syscomments). Это будет мешать другим выполнять подобные запросы.
Вывод
Вообще говоря, использования временных таблиц следует по возможности избегать. Если вам все же необходимо создать временную таблицу, то следуйте вышеперечисленным правилам, чтобы оказать мимнимальное влияние на производительность сервера.
Dinesh Asanka (оригинал: Temp Tables in SQL Server)
Перевод: Лысенко О.В.
Оригинал перевода