Sql временная таблица: MS SQL Server и T-SQL

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

Бывает, хочется собрать данные из нескольких таблиц в одну, и дальше с ними поработать. Например, выбрать вакансии вместе с работодателями и регионами:

select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id

Вопрос, как дальше запускать запросы по сводным данным. Есть три способа это сделать:

1. Табличное выражение (CTE)
2. Представление (view)
3. Временная таблица (temporary table)

Табличное выражение — это подзапрос с именем:

with combined_cte as (
  select v.*, e.name, a.name
  from vacancy as v
    join employer as e on e.id = v.employer_id
    join area as a on a.id = v.area_id
)
select ...
from combined_cte
where ...
group by ...
order by ...

Я подробно описывал CTE в отдельной заметке, так что не буду повторяться. Важно, что CTE каждый раз вычисляется заново, так что если наш сводный селект не особо быстрый — запросы по нему будут тормозить.

Представление работает как CTE, но к нему можно обращаться как к обычной таблице и не повторять каждый раз подзапрос. Как и CTE, представление тоже вычисляется на лету.

-- 1) create once
create view combined_view as
select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id;
-- 2) use everywhere
select ...
from combined_view
where ...
group by ...
order by ...

В PostgreSQL и других СУБД есть материализованные представления, которые сохраняют данные на диск. Но не в SQLite.

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

-- 1) create once
create temp table combined_temp as
select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id;
-- 2) use everywhere
select . ..
from combined_temp
where ...
group by ...
order by ...

Технически, SQLite хранит временные таблицы в специальной temp-базе. Она лежит в отдельном файле на диске и видна только тому соединению БД, в котором была создана. Как только соединение закрывается, SQLite автоматически удаляет временную базу.

Где находится временная база

На юниксо-подобных системах временная база лежит в одном из следующих каталогов:

1. Каталог, заданный настройкой PRAGMA temp_store_directory (объявлена устаревшей)
2. Переменная окружения SQLITE_TMPDIR
3. Переменная окружения TMPDIR
4. /var/tmp
5. /usr/tmp
6. /tmp
7. Текущий рабочий каталог (.)

SQLite выбирает первый вариант из списка, для которого есть разрешения на запись (write) и исполнение (execute).

Чтобы хранить временную базу в памяти, задайте настройку PRAGMA temp_store = MEMORY.

документация

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

Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀

НОУ ИНТУИТ | Лекция | Программные объекты

< Урок 23 || Урок 24: 123456 || Урок 25 >

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

Ключевые слова: глобальные временные таблицы, SQL, server, Transact-SQL, ограничение внешнего ключа, foreign, проверочное ограничение, analyzer, Object Browser, scalar, табличный тип данных, ServerName, статическая переменная, %ROWCOUNT, pane

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

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

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

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

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

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

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

CREATE TABLE NormalTable (theKey INT PRIMARY KEY, theValue CHAR(20))
CREATE TABLE #TamporaryTable (theKey INT PRIMARY KEY, theValue CHAR(20))

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

ru/2010/edi»>Временные таблицы всегда создаются в системной базе данных 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 (Открытие файла запроса).
3. Выберите сценарий CreateLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
4. В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос). Query Analyzer создаст временную таблицу.
5. В панели Object Browser выберите папку User Tables в базе данных tempdb.

   увеличить изображение

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

   увеличить изображение

Дальше >>

< Урок 23 || Урок 24: 123456 || Урок 25 >

sql server — Как удалить временную таблицу

спросил
3 года, 6 месяцев назад

Изменено
3 года, 6 месяцев назад

Просмотрено
2к раз

Временная таблица создается с помощью оператора SELECT . . INTO

 SELECT *
    В #MyTempTable
ОТ ...
 

В разделе Базы данных, tempdb, Временные таблицы я вижу временную таблицу
dbo.#MyTempTable____________________0000000016CA

Теперь я хочу удалить таблицу. Я пробовал следующее:

 УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ #MyTempTable
 

И

 ЕСЛИ OBJECT_ID('tempdb..#MyTempTable') НЕ NULL
НАЧИНАТЬ
    УДАЛИТЬ ТАБЛИЦУ #MyTempTable
КОНЕЦ
 

Оба не удалили таблицу

Выбор возвращает NULL:

 SELECT OBJECT_ID('tempdb..#MyTempTable')
 

• sql-сервер
• sql-сервер-2016

7

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

 ЕСЛИ OBJECT_ID('tempdb..#MyTempTable', 'U') НЕ НУЛЬ
НАЧИНАТЬ
    УДАЛИТЬ ТАБЛИЦУ #MyTempTable;
КОНЕЦ
 

Или упрощенно:

 ЕСЛИ OBJECT_ID('tempdb. .#MyTempTable', 'U') НЕ NULL
    УДАЛИТЬ ТАБЛИЦУ #MyTempTable;
 

4

Обратите внимание на имя проверяемой таблицы и отброшенной таблицы. #МояВременнаяТаблица;

, если это не работает для вас, я думаю, что ваша проблема связана с областью и/или видимостью вашей временной таблицы.

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

сначала вы должны проверить, активен ли сеанс, в котором вы делаете SELECT INTO , когда вы хотите удалить таблицу.

если ответ да, то проверьте, сбрасываете ли вы из того же сеанса или из другого.

во втором случае можно решить проблему используя ##MyTempTable вместо #MyTempTable

иначе это значит что что-то или кто-то уже сбросил

4

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Область временных таблиц в SQL Server

спросил
9 лет, 7 месяцев назад

Изменено
4 года, 5 месяцев назад

Просмотрено
60 тысяч раз

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

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

---

Вопросы:

• Правда ли, что временная таблица будет общей, если мы запустим два разных импорта одновременно?
• Создает ли каждый вызов EXEC новую область действия?

---

Вот надуманный пример скрипта.

 CREATE PROC [dbo].[ImportCompany]
(
    @CompanyId КАК ЦЕЛОЕ ЧИСЛО
)
КАК
EXEC [dbo].[Адрес Импорта] @CompanyId = @CompanyId
--Импорт других данных
CREATE PROC [dbo].[Адрес_импорта]
(
    @CompanyId КАК ЦЕЛОЕ ЧИСЛО
)
КАК
    CREATE TABLE #Companies (OldAddress NVARCHAR(128), NewAddress NVARCHAR(128))
    ВСТАВИТЬ В #Компании(СтарыйАдрес, НовыйАдрес)
    ВЫБИРАТЬ
        Адрес как OldAddress,
        «Преобразованный» + адрес как NewAddress
    ОТ
        [OldDb]. [dbo].[Адреса]
    ГДЕ
        CompanyId = @CompanyId
    -- Делать что-то с преобразованными данными
    DROP TABLE #Компании
EXEC [dbo].[ImportCompany] @CompanyId = 12345
 

• sql
• sql-сервер
• tsql
• sql-server-2012

Из СОЗДАТЬ ТАБЛИЦУ :

Локальные временные таблицы видны только в текущем сеансе

и (что более важно):

Если локальная временная таблица создается в хранимой процедуре или приложении, которое может выполняться одновременно несколькими пользователями, Компонент Database Engine должен иметь возможность различать таблицы, созданные разными пользователями [так в оригинале — почти наверняка здесь следует говорить о сеансах, а не о пользователях] . Компонент Database Engine делает это путем внутреннего добавления числового суффикса к каждому имени локальной временной таблицы.

Что в точности опровергает мнение того, кто сказал, что они будут общими.

---

Кроме того, нет необходимости DROP TABLE в конце вашей процедуры (снова по той же ссылке):

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

2

## используется для глобальных временных таблиц — будут доступны различные импорты.

# используется для локальных временных таблиц и доступен только в текущей/внутренней области.

1

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

Исключением являются глобальные временные таблицы, которые начинаются с ## . Они видны всем соединениям.

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