Где хранятся временные таблицы sql server

База данных tempdb в параллельном хранилище данных

tempdb — это системная база данных SQL Server PDW, в которой хранятся локальные временные таблицы для пользовательских баз данных. Временные таблицы часто используются для повышения производительности запросов. Например, можно использовать временную таблицу для модульизации скрипта и повторного использования вычисляемых данных.

Дополнительные сведения о системных базах данных см. в разделе «Системные базы данных».

Ключевые термины и понятия

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

Каждый сеанс может просматривать метаданные для локальных временных таблиц во всех сеансах. Например, все сеансы могут просматривать метаданные для всех локальных временных таблиц с запросом SELECT * FROM tempdb.sys.tables .

глобальная временная таблица
Глобальные временные таблицы, поддерживаемые в SQL Server с синтаксисом ##, не поддерживаются в этом выпуске SQL Server PDW.

pdwtempdb
pdwtempdb — это база данных, в которой хранятся локальные временные таблицы.

PDW не реализует временные таблицы с помощью базы данных tempdb SQL Server. Вместо этого PDW сохраняет их в базе данных с именем pdwtempdb. Эта база данных существует на каждом вычислительном узле и невидима для пользователя через интерфейсы PDW. В консоли Администратор на странице хранилища вы увидите эти учетные записи в системной базе данных PDW с именем tempdb-sql.

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

SQL Server PDW удаляет таблицы из tempdb , когда:

Выполняется инструкция DROP TABLE.
Сеанс отключен. Удаляются только временные таблицы для сеанса.
(модуль) завершает работу.
Узел управления имеет отработку отказа кластера.

Общие замечания

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

Ограничения

Ограничения и ограничения базы данных tempdb SQL Server PDW. Невозможно :

Создайте глобальную временную таблицу, начинающуюся с ##.
Выполните резервное копирование или восстановление tempdb.
Измените разрешения на tempdb с помощью инструкций GRANT, DENY или REVOKE .
Выполните DBCC SHRINKLOG для tempdb tempdb.
Выполнение операций DDL в tempdb. Существует несколько исключений для этого. Дополнительные сведения см. в следующем списке ограничений и ограничений для локальных временных таблиц.

Ограничения и ограничения для локальных временных таблиц. Невозможно :

Переименование временной таблицы
Создание секций, представлений или некластеризованных индексов во временной таблице. ALTER INDEX можно использовать для перестроения кластеризованного индекса для таблицы, созданной с помощью одной.
Измените разрешения на временные таблицы с помощью инструкций GRANT, DENY или REVOKE.
Запустите команды консоли базы данных во временных таблицах.
Используйте одно и то же имя для двух или нескольких временных таблиц в одном пакете. Если в пакете используется несколько локальных временных таблиц, они должны иметь уникальные имена. Если несколько сеансов выполняют один пакет и создают одну и ту же локальную временную таблицу, SQL Server PDW внутренне добавляет числовой суффикс к имени локальной временной таблицы, чтобы сохранить уникальное имя для каждой локальной временной таблицы.

Вы можете создавать и обновлять статистику во временной таблице.ALTER INDEX можно использовать для перестроения кластеризованного индекса.

Разрешения

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

Связанные задачи

Задачи	Description
Создайте таблицу в tempdb.	Можно создать временную таблицу пользователя с помощью инструкций CREATE TABLE и CREATE TABLE AS SELECT. Дополнительные сведения см. в статье CREATE TABLE and CREATE TABLE AS SELECT.
Просмотрите список существующих таблиц в tempdb.	SELECT * FROM tempdb.sys.tables;
Просмотрите список существующих столбцов в tempdb.	SELECT * FROM tempdb.sys.columns;
Просмотрите список существующих объектов в tempdb.	SELECT * FROM tempdb.sys.objects;

Связанный контент

SQL-Ex blog

Что использовать — табличную переменную или временную таблицу?

Добавил Sergey Moiseenko on Суббота, 26 августа. 2023

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

@Табличные переменные

Табличные переменные объявляются с использованием символа «@» и создаются в памяти. Они подобны обычным переменным в том, что их область действия ограничена пакетом или хранимой процедурой, в которой они объявлены, и их значениями можно манипулировать с помощью стандартных операторов SQL. Табличные переменные часто используются для хранения наборов данных небольших или средних размеров.

Преимущества табличных переменных:

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

Ограничения табличных переменных:

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

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

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

Преимущества временных таблиц:

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

Ограничения временных таблиц:

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

Когда использовать табличные переменные, а когда временные таблицы

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

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

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

Переменные SQL в скриптах, функциях, хранимых процедурах, SQLCMD и т.д.
Есть ли польза от удаления временной таблицы в хранимой процедуре?
Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще
Как использовать функциональность массивов в SQL Server?

Отличие способов хранения результирующих данных в T-SQL

Временные таблицы бывают двух видов. Таблицы переменные(@Table), временные таблицы(#table), ещё есть таблицы вида ##table, отличаются от #table областью видимости.

В связи с этим, можно дать краткое описание:

Таблицы переменные — хранятся в оперативной памяти(если её хватает). Доступна в блоке кода, т.е. её можно переиспользовать в разных запросах. имеет локальную область видимости, так же как любая другая локальная переменная.
Временные таблицы. Хранятся в tempdb, имеют более широкую область видимости, а именно по всему стеку вызовов. Т.е. если процедура А создала таблицу #A, потом вызвала процедуру B, которая создала таблицу #B — то и А имеет доступ к #B(после вызова В) и В имеет доступ к #A. Так же на временные таблицы можно создавать индексы, триггеры и прочее, в отличии от таблиц переменных. Таблицы ##Table имеют глобальную область видимости. Если кто-то создал таблицу ##Table — её видят все сессии, а существует она до тех пор, пока «жива» хоть одна сессия, которая обращалась к этой таблице.
СТЕ. Тут область вилимости только внутри одного запроса! Т.е. переиспользовать результат нельзя. Более того, если вы обращаетесь к СТЕ несколько раз внутри одного апроса — она будет вычислена столько же раз! Есть недокументированные способы заставить оптимизатор запомнить СТЕ в оперативной памяти для повторного использования, но это совсем другая история:)
Курсоры. В общем это немного из другой оперы. Курсоры позволяют построчно обрабатывать данные и предназначены не для хранения. Внутри курсора можно вызывать выполнение процедур, чего нельзя делать в запросе.

Добавлю ещё своё субъективное мнение когда что нужно использовать.

Таблицы переменные. Когда нужно использовать небольшое количество данных. Например промежуточный результат сложного запроса записать в таблицу переменную, разбив тем самым сложный запрос на два простых.
Временные таблицы. Когда информации довольно много и/или её нужно передать в другое место выполнения. Эти таблицы ничем не отличаются от обычных таблиц, кроме того, что не нужно беспокоиться о их очищении и удалении.
СТЕ — когда нельзя использовать временные таблицы(т.е. такие места, которые обязывают нас использовать только один SQL запрос), Например, внутри тела табличной функции.
Курсоры — когда нельзя обойтись другими способами. Например, когда для каждой строки временного результата нужно запустить выполнение хранимой процедуры. В MS SQL курсоры обычно работают медленнее запросов. Так что елси есть возможность — лучше их избегать.

Временная таблица в базе данных SQL

Временная таблица SQL, также известная как temp table, — это таблица, которая создается и используется в контексте определенного сеанса или транзакции в системе управления базами данных (СУБД). Она предназначена для хранения временных данных, которые нужны на короткое время и не требуют постоянного хранения.

Временные таблицы создаются «на лету». Обычно они используются для выполнения сложных вычислений, хранения промежуточных результатов или манипулирования подмножествами данных во время выполнения запроса или серии запросов.

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

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

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

Временные таблицы могут использоваться в различных СУБД, таких как MySQL, PostgreSQL, Oracle, SQL Server и других, хотя синтаксис и возможности могут несколько отличаться в разных реализациях.

Как создается временная таблица SQL

От редакции Techrocks: о том, как вообще создаются таблицы, читайте в статье «Как создать таблицу в SQL (примеры с PostgreSQL и MySQL)».

Чтобы создать временную таблицу, можно использовать инструкцию CREATE TABLE с ключевым словом TEMPORARY или TEMP перед именем таблицы. Вот пример на языке SQL:

CREATE TEMPORARY TABLE temp_table ( id INT, name VARCHAR(50), age INT );

Инструкция CREATE TEMPORARY TABLE используется для создания временной таблицы.
temp_table — это имя, которое присваивается временной таблице. Имя можно выбрать любое.
Внутри круглых скобок мы определяем столбцы временной таблицы.
В данном примере временная таблица temp_table имеет три столбца: id типа INT, name типа VARCHAR(50) и age типа INT.
При необходимости мы можем добавить дополнительные столбцы, указав их имена и типы данных.
Временная таблица автоматически удаляется в конце сеанса или при завершении сеанса.

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

Анализ подмножеств данных

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

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

-- Создать временную таблицу с подмножеством данных CREATE TEMPORARY TABLE subset_data AS SELECT column1, column2, column3 FROM original_table WHERE condition; -- Анализ подмножества данных SELECT column1, AVG(column2) AS average_value FROM subset_data GROUP BY column1; -- Удалить временную таблицу DROP TABLE subset_data;

Повышение производительности запросов

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

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

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

-- Создать временную таблицу для хранения промежуточных результатов CREATE TEMPORARY TABLE temp_results AS SELECT column1, COUNT(*) AS count_value FROM large_table WHERE condition1 GROUP BY column1; -- Использовать временную таблицу для оптимизации итогового запроса SELECT column1, column2 FROM temp_results WHERE count_value > 10 ORDER BY column1; -- Удалить временную таблицу DROP TABLE temp_results;

От редакции Techrocks: о том, как вообще делать запросы, читайте в статье «Запросы SQL: руководство для начинающих».

Подготовка и преобразование данных

Временные таблицы также полезны для подготовки и преобразования данных перед их загрузкой в постоянные таблицы.

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

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

-- Создать временную таблицу для подготовки данных CREATE TEMPORARY TABLE staging_table ( id INT, name VARCHAR(50), quantity INT ); -- Импортировать и преобразовать данные в подготовительную таблицу INSERT INTO staging_table (id, name, quantity) SELECT id, UPPER(name), quantity * 2 FROM external_source; -- Валидация и манипуляции с данными в подготовительной таблице UPDATE staging_table SET quantity = 0 WHERE quantity < 0; -- Вставить преобразованные данные в итоговую таблицу INSERT INTO final_table (id, name, quantity) SELECT id, name, quantity FROM staging_table; -- Удалить временную таблицу DROP TABLE staging_table;

Чем отличаются временная и постоянная таблицы в SQL

Критерий	Временная таблица	Постоянная таблица
Продолжительность жизни	Временная таблица существует только в текущей сессии или при текущем соединении	Сохраняется после завершения сессии или соединения
Сохранение данных	После завершения сеанса данные не сохраняются	Данные хранятся постоянно
Место хранения	Временное хранилище обычно располагается в памяти или пространстве для временного хранения	Постоянное хранилище размещается на диске или в базе данных
Доступность	Временная таблица доступна только для сессии или соединения, в которых создана	Постоянная таблица доступна для всех пользователей и соединений с соответствующими правами
Соглашение об именах	Имена временных таблиц часто имеют префиксы в виде специальных символов или ключевых слов	Имена постоянных таблиц не имеют префиксов в виде специальных символов или ключевых слов
Удержание данных	Данные автоматически удаляются в конце сессии или при закрытии соединения	Данные хранятся до тех пор, пока не будут намеренно удалены или изменены
Индексы и связи	Временные таблицы могут иметь индексы и связи, но они обычно временные (удаляются вместе с таблицей)	Постоянные таблицы могут иметь индексы, связи и триггеры
Свойства транзакций	По умолчанию временные таблицы не транзакционные, но это зависит от СУБД	Постоянные таблицы участвуют в транзакциях и поддерживают свойства ACID

Заключение

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