Ms sql временные таблицы: MS SQL Server и T-SQL

Содержание

Временные таблицы в функциях

← →
NewDelpher ©

(2004-10-04 15:35)
[0]

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

← →
Fay ©

(2004-10-04 15:39)
[1]

>> MS SQL не разрешает создавать временные таблицы в хранимых функциях
Это очень спорное утверждение. Откуда такая информация? Вы сами проверяли? Думаю, нет.

← →
Ega23 ©

(2004-10-04 15:41)
[2]

Она тебе набор данных возвратит.

1. Можешь временные таблицы ##ttt типа использовать — они «живут» пока сервер не перезапустят
2. Можешь временные таблицы #ttt типа использовать, если всё в рамках одной транзакции.

← →
NewDelpher ©

(2004-10-04 15:50)
[3]

в функции пишу:

SELECT number INTO #tmp_numbs
FROM mytable WHERE status= 1 and datenumb = @date_numb

при проверке синтаксиса
выдает:
Cannot accsess temporary tables from within a function

← →
Polevi ©

(2004-10-04 15:50)
[4]

>MS SQL не разрешает создавать временные таблицы в хранимых функциях

не разрешаются DDL выражения,
заменяй CREATE TABLE #T(…) на DECLARE @T TABLE (..)

← →
NewDelpher ©

(2004-10-04 15:58)
[5]

> не разрешаются DDL выражения,
> заменяй CREATE TABLE #T(. ..) на DECLARE @T TABLE (..)

тогда так мой запрос будет выглядеть:

SELECT number INTO @tmp_numbs
FROM mytable WHERE status= 1 and datenumb = @date_numb

← →
Fay ©

(2004-10-04 16:03)
[6]

Почему бы не почитать BOL?

← →
NewDelpher ©

(2004-10-04 16:08)
[7]

> Fay © (04.10.04 16:03) [6]
> Почему бы не почитать BOL?

Почитал, только не понял, их удалять получается не надо при выходе из функции?

← →
Nikolay M. ©

(2004-10-04 16:14)
[8]

> Почитал, только не понял, их удалять получается не надо
> при выходе из функции?

@T TABLE (. .) — это не таблица, а переменные табличного типа, поэтому удалять их не нужно.

← →
NewDelpher ©

(2004-10-04 16:17)
[9]

спасибо, разобрался

← →
Polevi ©

(2004-10-04 16:24)
[10]

>Nikolay M. © (04.10.04 16:14) [8]
нет таблица 🙂

← →
Nikolay M. ©

(2004-10-04 17:25)
[11]

> Polevi © (04.10.04 16:24) [10]

Аргументы? DECLARE @T TABLE (…) просто по конструкции декларирует переменную и в БОЛ о DECLARE TABLE написано:

Defines the table data type. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, NULL, and CHECK.

← →
Polevi ©

(2004-10-05 11:27)
[12]

внутрях все равно это временная таблица

DECLARE @T TABLE (ID INT PRIMARY KEY CLUSTERED)
INSERT @T SELECT 1
INSERT @T SELECT 1

← →
Nikolay M. ©

(2004-10-05 11:36)
[13]

> Polevi © (05.10.04 11:27) [12]

Где конкретно внутрях? В оперативке? В темпдб не увидел.

Правда ли, что в MS SQL Server создавать временные таблицы лучше вне транзакции? — Хабр Q&A

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

Для одиночных инструкций(одного запроса SELECT,INSERT,UPDATE,DELETE) существует альтернатива — Обобщенные табличные выражения(CTE) — link

Разумеется если временная таблица вам нужна больше чем на 1 запрос — такой подход неприменим.

Ответ написан

более трёх лет назад

Есть тонкость — распределённые транзакции. В MS SQL Server происходит эскалация транзакции даже в том случае, если в рамках транзакции идёт обращение к двум разным БД на одном сервере. Поскольку временные таблицы создаются в отдельной БД, при некоторых условиях могут возникать проблемы с MSDTC. Эскалация была значительной проблемой в 2005-й версии, но мы не так давно сталкивались с ней на 2008-й.

Ответ написан

более трёх лет назад

Нет это не правда, соответствено на сайте майкрософта не найдешь

Ответ написан

2016, в 11:34″>
более трёх лет назад

Комментировать

Ни разу не специалист по SQL (потому как fool stack developer), так что воспринимайте моё мнение критично. Но вопрос заинтересовал.

Не вижу причины отказываться от транзакций в C# коде, если это легаси проект. Наверняка там есть ещё чем заняться. Сам старался не использовать, хотя бывало. Зачастую, оборачивал группу хранимых процедур хранимкой с транзакцией, которую и вызывал из C#.

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

Временные таблицы стараюсь не использовать. Хотя если объём временных данных большой и нужны индексы, почему нет. Бывало, что злоупотребление ими серьёзно просаживало производительность, бывало и наоборот. Недавно надо было предавать данные между базами одного инстанса. Сначала использовал временные таблицы. Потом, по совету архитектора, переписал и стал пересылать через xml параметр (там это хорошо вписывалось в общую канву).

Ответ написан

более трёх лет назад

Комментировать

Оператор SELECT INTO TEMP TABLE в SQL Server

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

Введение

Оператор SELECT INTO — это один из простых способов создать новую таблицу, а затем скопировать в нее данные исходной таблицы.
только что созданная таблица. Другими словами, оператор SELECT INTO выполняет комбинированную задачу:

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

Мы можем использовать оператор SELECT INTO TEMP TABLE для выполнения вышеуказанных задач в одном операторе для временного
столы. Таким образом, мы можем быстро скопировать данные исходной таблицы во временные таблицы.

Синтаксис оператора SELECT INTO TEMP TABLE

ВЫБРАТЬ * | Column1, Column2… ColumnN

INTO #TempDestinationTable

FROM Source_Table

WHERE Condition

Аргументы SELECT INTO TEMP TABLE

Список столбцов: Мы можем использовать звездочку (*) для создания полной временной копии исходной таблицы или можем выбрать определенные столбцы исходной таблицы
Таблица назначения: Эта таблица ссылается на имя временной таблицы, в которую мы будем создавать и вставлять данные. Мы можем указать целевую таблицу как локальную или глобальную временную таблицу. Для локальной временной таблицы мы используем один знак решетки (#), а для глобальной временной таблицы мы используем знак решетки (##).
Исходная таблица: Источник — это таблица, из которой мы хотим считать данные
Где пункт: Мы можем использовать предложение where, чтобы применить фильтр к данным исходной таблицы.

В следующем примере мы вставим данные таблицы Location в таблицу #TempLocation. Другими словами, мы
создаст временный клон таблицы Location.

ВЫБЕРИТЕ * INTO #TempLocation FROM Production.Location

ПЕРЕЙТИ

SELECT * FROM #TempLocation

Как мы видим, оператор SELECT INTO создает таблицу #TempLocation, а затем вставляет в нее данные таблицы Location.

Когда мы хотим вставить определенные столбцы таблицы Location во временную таблицу, мы можем использовать следующее
запрос :

SELECT LocationID, Name, ModifiedDate INTO #TempLocationCol FROM Production. Location

ВЫБЕРИТЕ * ИЗ #TempLocationCol

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

ВЫБЕРИТЕ LocationID AS [TempLocationID],

Имя AS [TempLocationName] ,ModifiedDate AS [TempModifiedDate]

INTO #TempLocationCol FROM Production.Location

SELECT * FROM #TempLocationCol

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

SELECT LocationID, Name, ModifiedDate INTO #TempLocationCon FROM Production. Location

WHERE Name LIKE ‘F%’

SELECT * FROM #TempLocationCon

INSERT INTO SELECT vs SELECT INTO TEMP TABLE

Оператор INSERT INTO SELECT считывает данные из одной таблицы и вставляет их в существующую таблицу. Например, если мы хотим
скопируйте данные таблицы Location во временную таблицу с помощью инструкции INSERT INTO SELECT, мы должны указать
временную таблицу явно, а затем вставить данные.

11 0003

— Объявите временную таблицу —

Создание таблицы #Copylocation (

LocationId Smallint Not Null,

Название Nvarchar (50) Не нулевая,

Costrate Smallmone 2) НЕ НУЛЕВОЕ,

ModifiedDate datetime NOT NULL)

— Копировать данные во временную таблицу —

INSERT INTO #CopyLocation

SELECT * FROM Production. Location

— Выберите данные из временной таблицы

— 9000 —

ВЫБРАТЬ * ИЗ #CopyLocation

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

ВСТАВИТЬ В ВЫБОР	ВЫБЕРИТЕ
Требуется явно объявить целевую временную таблицу. Таким образом, это позволяет гибко изменять типы данных столбца и возможность создавать индексы.	Автоматически создает целевую временную таблицу.
Благодаря гибкости определения типов данных столбцов позволяет передавать данные из разных таблиц.	Он может создать резервную копию таблицы с простым синтаксисом.

SELECT INTO TEMP TABLE производительность

Оператор SELECT INTO TEMP TABLE выполняет две основные задачи в контексте производительности, а именно:

Чтение данных из исходных данных
Вставка данных во временную таблицу

Производительность операции чтения данных зависит от производительности запроса select, поэтому нам необходимо оценить производительность
процесс чтения данных в этой области. Однако конфигурация базы данных tempdb повлияет на
производительность оператора вставки. В SQL 2014 операторы SELECT … INTO выполняются параллельно, поэтому
они показывают лучшую производительность. Теперь давайте проанализируем следующий план выполнения запроса.

ВЫБЕРИТЕ SalesOrderID, CarrierTrackingNumber, ModifiedDate

INTO #TempsSalesDetail FROM Sales. SalesOrderDetail

ЗАКАЗ ПО SalesOrderID

1- Оператор Clustered Index Scan считывает все данные из первичного ключа таблицы SalesOrderDetail и передает
все данные в таблицу оператора вставки.

2- Оператор Table Insert добавляет новые данные во временную таблицу и выполняет эту операцию параллельно.
способ. Эту ситуацию можно отобразить в атрибуте Фактическое количество строк. Поток 0 не показывает никаких значений
потому что это поток координатора.

Оператор Gather Stream объединяет несколько параллельных операций в одну операцию. В этом плане выполнения запроса
мы использовали предложение ORDER BY, но мы не видим никакого оператора в плане выполнения. В то же время
Оператор Clustered Index Scan не возвращает результат в отсортированном виде. Причина этого пункта в том, что нет
гарантия порядка вставки строк в таблицу.

Заключение

В этой статье мы изучили синтаксис и детали использования инструкции SELECT INTO TEMP TABLE. Этот
Оператор очень удобен для вставки данных таблицы или данных запроса во временные таблицы.

Автор
Последние сообщения

Esat Erkec

Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.

Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Просмотреть все сообщения от Esat Erkec

Последние сообщения от Esat Erkec (посмотреть все)

r — временные таблицы sql в блоках sql ноутбука rstudio?

Задавать вопрос

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

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

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

Часть R Language Collective Collective

Я пытаюсь использовать временные таблицы в фрагменте кода sql в rstudio.

Пример: когда я выбираю одну таблицу и возвращаю ее в объект r, кажется, что все работает:

 ```{sql , output.var="x", connection='db' }
  ВЫБЕРИТЕ количество (*) n
    ИЗ исходной базы данных
```

Когда я пытаюсь что-то сделать с временными таблицами, кажется, что команды выполняются, но возвращает пустой r data.frame

 ```{sql , output.var="x", connection='db' }
  ВЫБЕРИТЕ количество (*) n
    В #что угодно
    ИЗ исходной базы данных
  ВЫБИРАТЬ *
   ОТ #что угодно
```

У меня сложилось впечатление, что блоки sql ноутбука Rstudio просто настроены на выполнение одного запроса. Поэтому мое временное решение — создать таблицы в хранимой процедуре в базе данных. Тогда я смогу получить желаемые результаты с помощью чего-то простого. Я бы предпочел иметь немного больше гибкости в кусках кода sql.

мое соединение с БД выглядит так:

 ```{r,echo=F}
db <- DBI::dbConnect(odbc::odbc(),
                      драйвер = "SQL-сервер",
                      сервер = 'sql',
                      база данных = 'имя_базы_данных')
```

sql
р
рстудия

Как и этот вопрос, он будет работать, если вы поставите

 set nocount на

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

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

 С CTE_WHATEVER КАК (
                       ВЫБЕРИТЕ СЧЕТ(*) n
                       ИЗ исходной базы данных
                      )
      ВЫБИРАТЬ *
      ОТ CTE_WHATEVER

Вы также можете сделать это для нескольких примеров временных таблиц

 С CTE1 КАК (
              ВЫБИРАТЬ
                  СОСТОЯНИЕ
                 ,ОКРУГ
                 ,СЧЁТ(*) п
              ИЗ исходной базы данных
              ГРУППА ПО
                 СОСТОЯНИЕ
                 ,ОКРУГ
              ),
     CTE2 АС (
              ВЫБИРАТЬ
                 СОСТОЯНИЕ
                 ,среднее(n)
                 ,COUNTY_AVG
              ОТ CTE1
              ГРУППА ПО
                 СОСТОЯНИЕ
              )
ВЫБИРАТЬ *
ОТ CTE2
ГДЕ COUNTY_AVG > 1000000

Извините за форматирование.