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

Содержание

Табличные функции и временные таблицы в Transact-SQL | Info-Comp.ru

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

Содержание

  1. Табличные функции в Transact-SQL – описание и примеры создания
  2. Пример создания простой табличной функции
  3. Пример создания табличной функции, в которой можно программировать
  4. Временные таблицы в Transact-SQL — описание и пример создания

Табличные функции в Transact-SQL – описание и примеры создания

Раньше мы уже знакомились с функциями, которые возвращают таблицу, правда, на языке PL/pgSQL для сервера PostgreSQL (Написание табличной функции на PL/pgSQL). Теперь пришло время поговорить о такой реализации на Transact-SQL.

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

Пример создания простой табличной функции

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

Примечание! Данный пример можно реализовать и с помощью представления. Но мы пока только учимся писать такие функции.

   
   --название нашей функции
   CREATE FUNCTION [dbo]. [fun_test_tabl]  
   (    
   --входящие параметры и их тип
   @id INT
   )
   --возвращающее значение, т.е. таблица
   RETURNS TABLE
   AS
   --сразу возвращаем результат
   RETURN 
   (
        --сам запрос
        SELECT * FROM table WHERE id = @id
   )
   GO

В итоге мы создали функцию, в которую будем передавать один параметр id, его мы используем в условии исходного SQL запроса.

Получить данные из этой функции можно следующим образом:

   
   SELECT * FROM dbo.fun_test_tabl (1)

Как видите все проще простого. Теперь давайте создадим функцию уже с использованием программирования в этой функции.


Пример создания табличной функции, в которой можно программировать

   
   --название нашей функции
   CREATE FUNCTION [dbo].[fun_test_tabl_new]  
   (    
   --входящие параметры
   @number INT
   )
   --возвращающее значение, т. е. таблица с перечислением полей и их типов
   RETURNS @tabl TABLE (id INT, number INT, summa MONEY)
   AS
   BEGIN
           --объявляем переменные
           DECLARE @var MONEY
           --выполняем какие-то действия на Transact-SQL
           IF @number >=0 
           BEGIN 
                SET @var=1000
           END
           ELSE
                        SET @var=0
                --вставляем данные в возвращающий результат
                INSERT @tabl
                        SELECT id, number, summa 
                        FROM tabl 
                        WHERE summa > @var
           --возвращаем результат
           RETURN 
   END

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

Теперь давайте обратимся к нашей функции, например, вот так

   
   SELECT * FROM dbo.fun_test_tabl_new (1)

Временные таблицы в Transact-SQL — описание и пример создания

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

Например, в процедуре Вам необходимо выполнять много расчетов и запоминать их результат, а создавать дополнительные представления, табличные функции или вообще таблицы, которые потом нужно удалять, так как они Вам после выполнения процедуры будут не нужны, Вам просто не охота, да и это не очень удобно. Поэтому в Microsoft SQL Server существует такая возможность как «Временные таблицы». Давайте научимся их создавать. А создавать и использовать их очень просто, например, в коде своей процедуры, Вы хотите сохранить результат в таблицу, для этого просто создаете эту таблицу вот так (перед названием таблицы ставится знак решетки #).

   
   CREATE TABLE #tmp(column1 INT, column2 INT, column3 MONEY)
        INSERT INTO #tmp(column1, column2, column3)
                SELECT data1, data2, data3 
                FROM table
   --Затем по коду ниже Вы ее можете использовать, а в конце процедуры удалить ее 
   DROP TABLE #tmp

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

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

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

Удачи!

Применение Database Engine Tuning Advisor в оптимизации производительности запросов 1С 8.1, 1С 8.2

В данной статье рассматривается, насколько применим инструмент Database Engine Tuning Advisor, входящий в MS SQL Server 2005, при решении проблемы производительности запросов, генерируемых платформой 1С 8.1 и 1С 8.2.

При этом не ставилось целью провести детальный анализ всех возможностей данного инструмента, или сравнить его с какими-то альтернативными средствами. Это слишком большие вопросы, достойные отдельного исследования. В статье рассмотрен более узкий вопрос: принципиальная возможность использования Tuning Advisor с учетом специфики команд, используемых платформой 1С 8. 1 и 1С 8.2.

Содержание статьи

  • Дана общая характеристика области применения и основных функциональных возможностей Tuning Advisor.

  • Описаны эксперименты по его использованию при анализе профиля нагрузки 1С 8.х., а также эксперименты, демонстрирующие работу Tuning Advisor с командами, использующими временные таблицы.

  • Описаны некоторые особенности работы менеджера временных таблиц 1С 8.х.

  • Показано, что эти особенности сильно ограничивают применимость Tuning Advisor для оптимизации запросов 1С 8.1 и 1С 8.2.

  • Приведён подробный пример того, каким образом платформа 1С 8.1 и 1С 8.2 создаёт временную таблицу при явном её задании в пользовательской выборке данных.

  • Сделаны обобщающие выводы.

Краткое описание Tuning Advisor

Программное средство Database Engine Tuning Advisor, входящее в MS SQL Server 2005, пришло на смену мастеру Index Tuning Wizard, который входил в MS SQL Server 2000. Этот инструмент предназначен для того, чтобы облегчить работу по оптимизации индексов и других структур в базе данных. В качестве исходной информации для анализа он может принимать файл или таблицу трассировки, созданную при помощи профилировщика (Profiler), а также любой текстовый файл с командами T-SQL. Обычно в таком файле или таблице собирается последовательность команд, выполнявшихся в процессе работы пользователей на SQL-сервере (профиль нагрузки) за какой-то промежуток времени (например, за рабочий день). Исходная информация анализируется в соответствии с заданными параметрами (рассчитываются различные варианты внесения изменений в индексы и статистики), и по результатам анализа генерирует отчёт и рекомендации. Анализ происходит в автоматическом режиме, рекомендации можно сохранить в файл и применить в любое время. Есть возможность предварительной оценки сценариев до их применения в базе данных (что-то вроде «А что будет, если этот индекс добавить, а этот — удалить?»).

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

Проверим, насколько эффективен Tuning Advisor при анализе типичного профиля нагрузки, создаваемого при работе пользователей в 1С 8. 1 и 1С 8.2.

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

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

Однажды созданная временная таблица не удаляется после использования полностью. Происходит только удаление данных, а структура таблицы хранится в памяти до тех пор, пока она вновь не потребуется в данном соединении (SPID). Каждый раз, когда возникает необходимость во временной таблице, менеджер сначала проверяет, нет ли готовой таблицы с подходящей структурой. Если подходящая таблица есть, то используется она, а если нет – то лишь в этом случае создаётся новая временная таблица.

Очевидно, что данная особенность наложит определённые ограничения на использование Tuning Advisor. Если запустить трассировку спустя какое-то время после начала работы пользователей 1С, то в трассу не попадут команды создания временных таблиц, уже выполненные до запуска. Это значит, что Tuning Advisor не сможет проанализировать конструкции Transact-SQL, в которых встречаются эти временные таблицы. И таких конструкций может оказаться значительная часть.

Однако, если начать трассировку до того, как в 1С начнут работать пользователи, то в неё гарантированно попадут все команды создания всех временных таблиц. И при анализе такой трассы Tuning Advisor сможет корректно проанализировать все конструкции, в которых встречаются временные таблицы.

Анализ трассы 1С

Попробуем проанализировать в Tuning Advisor произвольную небольшую трассу, собранную с одной сессии 1С:Предприятие 8.х. Проведём эксперимент:

  1. Закрываем все сессии 1С:Предприятие 8.1 (или 1С 8.2) на тестовом сервере приложений

  2. Запускаем одну сессию 1С:Предприятие 8.1 (или 1С 8.2).

  3. Включаем трассировку MS Profiler, включив события

  4. Перепроводим произвольный документ «Поступление НМА»

  5. Сохраняем собранную трассу в файл

  6. Проверяем: действительно, все команды создания временных таблиц в трассу попали

  7. Анализируем полученный файл трассировки в Tuning Advisor

По завершении анализа Tuning Advisor в области Tuning Progress сообщил, что 18% обработанных операторов содержат ошибки (см. рисунок ниже).

А в области Tuning Log видно, что все конструкции, в которых фигурируют временные таблицы, вызвали ошибку: «Invalid object name {Название временной таблицы}». Т.е. Tuning Advisor по каким-то причинам не смог распознать ни одной временной таблицы, несмотря на то, что в трассе имеются команды создания каждой из них (проверено). Почему?

Работа с «select … into…»

Известно, что для создания временных таблиц 1С 8.1 или 1С 8.2 использует команду «select TOP 0 {столбцы} into #tt{N} from {Таблица}» (см. Tuning Log выше). Проверим, каким образом работает с такими конструкциями Tuning Advisor.

Для эксперимента возьмём простейший скрипт, в котором сначала создаётся и заполняется временная таблица, а затем выполняется её соединение с реальной таблицей:

select TOP 0 xtype [c1] into #tt1 from sysobjects (nolock)
go
insert #tt1 (c1) select top 1 xtype from sysobjects (nolock)
go
create unique clustered index idx1 on #tt1 (c1)
go
select a. *
from rs_subqueue_arch a (nolock)
inner join #tt1 b
ON a.oper = b.c1
Go

Проанализируем этот скрипт с помощью Tuning Advisor. В результате увидим, что так же, как в случае с трассировкой 1С, конструкция с временной таблицей вызвала ошибку.

Работа с «create table…»

Теперь заменим в скрипте команду «select… into…» на «create table…»:

create table #tt1 (c1 varchar(5))
go
insert #tt1 (c1) select top 1 xtype from sysobjects (nolock)
go
create unique clustered index idx1 on #tt1 (c1)
go
select a.*
from rs_subqueue_arch a (nolock)
inner join #tt1 b
ON a.oper = b.c1
Go

Модифицированный таким образом скрипт Tuning Advisor обработал без ошибок и выдал две рекомендации: создать по таблице [rs_subqueue_arch] индекс по полю [oper] и статистику по полям ([id],[oper]).

Следует отметить, что перед расчётами Tuning Advisor преобразует многие выражения, приводя их к некой нормальной форме. В данном случае в области Tuning Log видно, что выражение «select a.* from … join #tt1…» было преобразовано к виду «create table #tt1 (c1 int) select a.* from … join #tt1…».

Обобщение по «select … into…» и «create table…»

Обобщим результаты. Создание временной таблицы возможно двумя способами: явно («create table») и неявно («select…into»). Когда Tuning Advisor встречает в анализируемой трассе (скрипте) команду явного создания временной таблицы «create table #tt1…», он запоминает её, и далее использует при анализе других команд, в которых эта временная таблица встречается. При таком способе наличие временных таблиц не вызывает проблем, главное, чтобы в трассе присутствовали операторы их создания.

Иначе обстоит дело, когда временная таблица создаётся «неявно» — с помощью T-SQL конструкция «select TOP 0… into #tt1…from…» (именно такие конструкции использует платформа 1С 8.1 и 1С 8.2). В отличие от «create table #tt1…», команду «select TOP 0. .. into #tt1…» Tuning Advisor игнорирует и никак далее не учитывает. А когда в анализируемой трассе (скрипте) встречается временная таблица, созданная такой командой, Tuning Advisor не может разобрать соответствующую конструкцию, и сообщает о неопознанном объекте — «Invalid object name {Название временной таблицы}».

Таким образом, поскольку временные таблицы используются платформой 1С 8.1 и 1С 8.2 довольно часто, значительная часть команд 1С не может быть проанализирована в Tuning Advisor.

Пример, как платформа 1С создаёт временную таблицу

Для иллюстрации того, каким способом платформа 1С 8.1 и 1С 8.2 создаёт временные таблицы, приведём простой пример.

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

Первая выборка формирует временную таблицу, а вторая делает соединение этой временной таблицы со справочником Номенклатура.

На уровне SQL-сервера этим выборкам соответствуют команды, которые приведены на скриншоте MS Profiler:

  • «select TOP 0 . .. into #tt1 …» — создаётся пустая временная таблица

  • «exec sp_executesql N’INSERT INTO #tt1…» — временная таблица заполняется данными

  • «exec sp_executesql N’SELECT…FROM #tt1…» — итоговая выборка, выполняющая соединение временной таблицы со справочником.

Если трассу с этими командами проанализировать с помощью Tuning Advisor, то получится такой же результат, как в приводимых выше примерах: первый оператор «SELECT TOP 0…» пропускается, а два оператора, использующие временную таблицу, вызывают ошибку «Invalid object name #tt1».

 

В приведённом примере временная таблица была явно задана в выборке с помощью команды «ПОМЕСТИТЬ». Однако следует заметить, что использование временных таблиц далеко не ограничивается выборками данных, явно прописанными в коде конфигурации. Они также активно используются на уровне платформы 1С 8.1 и 1С 8.2. при выполнении самых различных операций. И во всех случаях для создания временной таблицы используется оператор «select TOP 0 … into». 

Удельное количество команд, использующих временные таблицы 

Проанализируем, каково удельное количество команд, использующих временные таблицы. Поскольку для оптимизации интересны в первую очередь наиболее ресурсоёмкие команды, будем выбирать только из команд, наиболее тяжелых по времени выполнения (Duration).

В качестве примера возьмём статистику, собранную программой мониторинга производительности «PerfExpert» по тяжёлым запросам (Duration>5сек) в течение рабочего дня в базе данных ЦО одного из клиентов.

Всего операторов 1С с Duration>5сек за выбранный период:

Отберём из общего числа операторы 1С, в которых фигурируют временные таблицы:

Количество команд с временными таблицами составило более 62% от общего числа, при этом их удельный вес по CPU составил почти 70%. Это означает, что большая половина тяжёлых команд, создающих большую часть нагрузки на процессор, и интересных с точки зрения оптимизации выполнения, не могут быть обработаны в Tuning Advisor.

Общий вывод по Tuning Advisor

Tuning Advisor является хорошим инструментом, имеющим массу достоинств, к основным из которых можно отнести:

  • анализ исходных данных производится в автоматическом режиме в соответствии с заданными настройками

  • от специалиста не требуется глубоких знаний в области оптимизации T-SQL-запросов

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

  • имеется возможность оценки сценариев, предлагаемых пользователем, например: «А что будет, если этот индекс добавить, а этот — удалить?».

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

Во-первых, возникает естественный вопрос, в каких единицах высчитывается вклад выражений в общую нагрузку? Понятно, что, скорее всего, это некая величина, агрегированная из показателей reads, cpu, writes (возможно ещё каких-то). Но, как именно она получается, не известно, алгоритм её вычисления закрыт.

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

И в-третьих, объединив информацию из нескольких отчётов, выданных в результате анализа, можно понять, какой индекс на каком выражении какой выигрыш даст. Но при этом Tuning Advisor информацию по одному и тому же параметризованному запросу, выполненному с разными наборами параметров, в отчётах приводит раздельно, не агрегируя сведения по аналогичным запросам.

Кроме того, при всех плюсах, применимость Tuning Advisor для анализа и оптимизации индексов в базах данных 1С 8.х сильно ограничена из-за специфики работы платформы 1С 8.х с временными таблицами. И в первую очередь из-за того, что для создания временных таблиц платформа 1С использует Transact-SQL конструкцию «SELECT … INTO…», которую Tuning Advisor не умеет корректно анализировать.

Использование этой конструкции для создания таблиц приводит к тому, что из поля зрения Tuning Advisor полностью выпадают все операторы, в которых фигурируют временные таблицы. Статистика, собранная на реальных производственных системах, показывает, что количество таких операторов достигает 60% и более от общего числа наиболее тяжёлых запросов.

Компания «СофтПоинт» разработала собственный инструментарий для помощи в оптимизации тяжелых запросов на MS SQL Server 2005/2008,. Помощник используется для оптимизации запросов, как в 1С 7.7, 8.1, так и в произвольных системах работающих на MS SQL Server 2005/2008. При этом инструментарий позволяет не только определять узкие места в запросах с детализацией до «узла» плана выполнения и предикатов, но и рекомендует какой индекс следует добавить, а также дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов. Помощник умеет анализировать временные таблицы, которые в спектре запросов 1С 8.1 могут составлять более 50%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте.

Временные таблицы и табличные переменные в процедурах T-SQL

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

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

Если вы хотите внести свой вклад, запросите приглашение, поставив лайк или ответив на эту статью.
Узнать больше

— Команда LinkedIn

Последнее обновление:
1 июня 2023 г.

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

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

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

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

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

        Недостатки табличных переменных

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

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

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

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

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

              Вот что еще нужно учитывать

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

                Оцените эту статью

                Мы создали эту статью с помощью ИИ. Что вы думаете об этом?

                Это здорово

                Это не так здорово

                Спасибо за ваш отзыв

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

                Типы временных таблиц SQL Server

                Автор: Rick Dobson   |
                Комментарии   | Похожие: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Подробнее > Временные таблицы

                Проблема

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

                Решение

                Причины временных хранилищ данных SQL Server

                Существует множество причин для создания и использования временных хранилищ данных.
                Некоторые типичные причины перечислены ниже.

                • Вам необходимо заполнить одну или несколько таблиц входными данными из нетабличного
                  источник данных или набор источников. Исходные нетабличные данные могут быть обработаны
                  в таблицу строковых значений перед извлечением, преобразованием и загрузкой
                  исходные данные в таблицы SQL Server.
                • Вам необходимо разработать отчеты на основе очень большой базы данных для одноразового
                  проект. Для отчетов требуется доступ только к небольшой части одного или нескольких
                  постоянные источники данных.
                • Запросу требуется доступ к небольшому подмножеству некоторого постоянного хранилища данных с
                  меньше столбцов и/или строк, чем в постоянном хранилище данных. Добыча
                  запрос достаточно сложен и требует последовательных раундов уточнения либо для
                  замысла и/или существенных причин. В такой ситуации временно
                  хранилище данных может повысить производительность по сравнению с последовательными раундами тестирования на
                  способ разработки окончательного запроса.
                • Иногда вы можете создать временное хранилище данных в блоке кода, например
                  хранимая процедура или некоторый динамический SQL, но вы также хотите поделиться этим временным
                  данные вне блока, в котором они были созданы. Вы можете использовать разные
                  типы временных хранилищ данных в зависимости от объема ваших требований к совместному использованию данных.
                • Вам нужно хранилище данных, которое изначально предназначено для дискретного периода, например
                  в конце смены, после закрытия торгов дня или в конце финансового
                  месяц. Этот тип хранилища данных является временным в том смысле, что он повторно заполняется.
                  со значениями для каждой новой смены, рабочего дня или финансового месяца.
                • Решению необходим доступ к промежуточным вычисляемым значениям. Фактически,
                  может быть несколько раундов или слоев промежуточных вычисляемых значений, которые в конечном итоге
                  поддерживать вычисление окончательного набора вычисляемых значений. Только финал
                  набор вычисленных значений предназначен для сохранения на постоянной основе.

                Типы временных хранилищ данных SQL Server

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

                • Временная таблица — это реальная таблица базы данных в постоянной базе данных. Все
                  временные таблицы находятся в базе данных tempdb, которая является системной базой данных.
                  Временная таблица является временной в том смысле, что она обычно становится недоступной, когда
                  подключение к базе данных для создания временной таблицы больше не существует.
                • Подзапросы — это операторы выбора, вложенные в другие операторы SQL или
                  предложения операторов SQL. Существует множество различных вариантов подзапросов,
                  и эти изменения могут повлиять на приложение для подзапроса.
                • Производная таблица — это особый тип подзапроса внутри предложения from.
                  оператора выбора. Этот тип подзапроса представляет собой код, который возвращает результат
                  набор, который действует как таблица во многих отношениях.
                • Табличная переменная имеет некоторые свойства, такие как локальные переменные и другие свойства.
                  как столы. Вы определяете табличную переменную с помощью оператора declare.
                  Его областью действия является пакет, хранимая процедура или функция, в которой объявление таблицы
                  проживает.
                • Общее табличное выражение (CTE) — это набор результатов, возвращаемый оператором select.
                  Он временный в том смысле, что его область действия ограничена завершающим элементом select,
                  оператор вставки, удаления или обновления. CTE часто используются для упрощения сложных
                  запросы. Кроме того, CTE иногда используются для рекурсивных запросов, а именно:
                  для запросов, которые вызывают сами себя.
                • Другой способ хранения временных данных — в постоянной таблице.
                  При таком подходе вы заполняете таблицу в пользовательской базе данных, а затем работаете с ней.
                  как требуется, и, наконец, удалить постоянную таблицу, когда временные данные
                  он больше не нужен, например, в конце приложения.

                В этой серии мы рассмотрим следующее:

                1. Временные таблицы
                2. Подзапросы
                3. Коррелированные подзапросы
                4. Производные таблицы
                5. Табличные переменные
                6. Общие табличные выражения (CTE)
                7. Постоянные временные таблицы данных
                Следующие шаги

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

                • Временные таблицы в SQL Server

                • INF: часто задаваемые вопросы — SQL Server 2000 — табличные переменные

                • Сравнение производительности — таблицы, временные таблицы и табличные переменные

                • Табличные переменные в T-SQL

                • Как использовать CTE SQL Server, чтобы сделать ваш код T-SQL доступным для чтения людьми

                Об авторе

                Рик Добсон — писатель и индивидуальный трейдер. Он также является профессионалом в области SQL Server с многолетним опытом работы с T-SQL, который включает в себя написание книг, проведение национальных семинаров, работу с предприятиями над проектами в области финансов и здравоохранения, а также является постоянным участником MSSQLTips.com. Он оттачивал свои навыки работы с Python в течение более чем пятидесяти лет, особенно для визуализации данных и задач ETL с файлами JSON и CSV.