В чем разница между CTE и Temp Table?
В чем разница между общим выражением таблицы (CTE) и временной таблицей? И когда я должен использовать один над другим?
CTE
WITH cte (Столбец1, Столбец2, Столбец3)
В ВИДЕ
(
SELECT Column1, Column2, Column3
FROM SomeTable
)
SELECT * FROM cte
Таблица Temp
SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable
SELECT * FROM #tmpTable
7 ответов
Это довольно широко, но я дам вам как общий ответ, как только смогу.
CTE ...
- Неинтенсивны (но могут использовать существующие индексы для ссылочных объектов)
- Не может быть ограничений
- В основном одноразовые
VIEWs - Сохранять только до следующего запуска запроса.
- Может быть рекурсивным
- Не имеют выделенной статистики (полагайтесь на статистику по базовым объектам)
#Temp Таблицы ...
- Реальные материализованные таблицы, которые существуют в tempdb
- Может быть проиндексирован
- Может иметь ограничения
- Устойчивость к жизни текущего СОЕДИНЕНИЯ
- Можно ссылаться на другие запросы или подпроцедуры
- Выделите статистику, созданную двигателем.
Что касается использования каждого из них, они имеют очень разные варианты использования. Если у вас будет очень большой набор результатов или вам нужно будет обращаться к нему более одного раза, поместите его в таблицу #temp. Если он должен быть рекурсивным, является одноразовым или просто упрощает что-то логически, предпочтительнее CTE.
Кроме того, CTE должен никогда не использоваться для производительности . Вы почти никогда не будете ускорять работу, используя CTE, потому что, опять же, это просто одноразовый вид. Вы можете делать с ними аккуратные вещи, но ускорение запроса на самом деле не является одним из них.
EDIT:
См. комментарии Мартина ниже:
CTE не является материализованным как таблица в памяти. Это всего лишь способ инкапсуляции определения запроса. В случае OP он будет встроен и будет таким же, как и выполнение
SELECT Column1, Column2, Column3 FROM SomeTable. Большую часть времени они не получают материализованного фронта, поэтому это не возвращает строкиWITH T (X) AS (SELECT NEWID ()) SELECT * FROM T T1 JOIN T T2 ON T1.X = T2.X, также проверьте планы выполнения. Хотя иногда можно взломать план, чтобы получить катушку. Для этого есть элемент подключения, запрашивающий подсказку. â € "Мартин Смит 15/02/12 в 17:08
Оригинальный ответ
CTE
CTE создает таблицу, которая используется в памяти, но действительна только для конкретного запроса, следующего за ней. При использовании рекурсии это может быть эффективной структурой.
Вы также можете рассмотреть возможность использования переменной таблицы. Это используется как используется временная таблица и может использоваться несколько раз без необходимости повторной материализации для каждого соединения. Кроме того, если вам нужно сохранить несколько записей сейчас, добавьте еще несколько записей после следующего выбора, добавьте еще несколько записей после другого op, а затем верните только эти несколько записей, тогда это может быть удобной структурой, так как она не работает После выполнения нужно отказаться. В основном просто синтаксический сахар. Однако, если вы держите число строк ниже, оно никогда не материализуется на диск. См. В чем разница между таблицей temp и табличной переменной в SQL Server? для более подробной информации.
Таблица темпов
Подробнее о MSDN - прокрутите вниз примерно на 40% пути
Таблица temp - это буквально таблица, созданная на диске, только в определенной базе данных, которую все знают, можно удалить. Ответственность за то, что эти таблицы больше не нужны, лежит на хорошем разработчике, но администратор базы данных также может стереть их.
Временные таблицы бывают двух видов: локальные и глобальные. В терминах MS Sql Server вы используете обозначение #tableName для локального и ## tableName для глобального (обратите внимание на использование одного или двойного # в качестве идентифицирующего признака ).
Обратите внимание, что с помощью temp-таблиц, в отличие от табличных переменных или CTE, вы можете применять индексы и тому подобное, поскольку они являются законными таблицами в нормальном смысле этого слова.
В общем, я бы использовал временные таблицы для более длинных или больших запросов, а также CTE или табличные переменные, если бы у меня был небольшой набор данных, и мне хотелось просто быстро скопировать немного кода на что-то маленькое. Опыт и советы других людей указывают на то, что вы должны использовать CTE, где у вас есть небольшое количество строк, возвращаемых из него. Если у вас большое количество, вы, вероятно, выиграете от возможности индексирования в таблице temp.
CTE может многократно вызываться в запросе и оценивается каждый раз, когда он ссылается - этот процесс может быть рекурсивным. Если он просто называется один раз, он ведет себя подобно подзапросу, хотя параметры CTE могут быть параметризованы.
Временная таблица физически сохраняется и может быть проиндексирована. На практике оптимизатор запросов может также сохранять промежуточные результаты соединения или подзапроса за кулисами, например, в операции буферизации, поэтому не совсем верно, что результаты CTE никогда не сохраняются на диске.
Табличные переменные IIRC (с другой стороны) всегда являются структурами в памяти.
Таблицы CTE и temp имеют очень разные варианты использования. Я просто хочу подчеркнуть, что, хотя это не панацея, понимание и правильное использование CTE может привести к некоторым действительно потрясающим улучшениям как качества кода, так и поддерживаемости и скорости. Поскольку я получил ручку на них, я вижу временные таблицы и курсоры как большие пороки обработки SQL. Я могу отлично справиться с табличными переменными и CTE для почти всего сейчас. Мой код чище и быстрее.
Таблица Temp - это реальный объект в tempdb, но cte - это всего лишь оболочка сложного запроса, чтобы упростить синтаксис организации рекурсии за один шаг.
Основной причиной использования CTE является доступ к таким функциям окна, как row_number () и другие.
Это означает, что вы можете делать что-то вроде первой или последней строки в группе ОЧЕНЬ ОЧЕНЬ быстро и эффективно - ответил Dave Hilditch 8 +04002013-10-08T04:50:20+04:00312013bEurope/MoscowTue, 08 Oct 2013 04:50:20 +0400 2013, 04:50:20
Здесь, похоже, есть немного негатива в отношении CTE.
Мое понимание CTE заключается в том, что это в основном своего рода adhoc-представление. SQL - это декларативный и основанный на наборе язык. CTE - отличный способ объявить набор! Невозможность индексировать CTE на самом деле хорошо, потому что вам не нужно! Это действительно своего рода синтаксический сахар, чтобы облегчить чтение и запись запроса. Любой достойный оптимизатор разработает лучший план доступа с использованием индексов в базовых таблицах. Это означает, что вы могли бы эффективно ускорить ваш запрос CTE, следуя рекомендациям индекса в базовых таблицах.
Кроме того, только потому, что вы определили набор как CTE, это не означает, что все строки в наборе должны обрабатываться. В зависимости от запроса оптимизатор может обрабатывать строки «достаточно», чтобы удовлетворить запрос. Возможно, вам нужны только первые 20 или около того для вашего экрана. Если вы создали временную таблицу, вам действительно нужно прочитать /записать все эти строки!
Основываясь на этом, я бы сказал, что CTE - отличная функция SQL и может использоваться везде, где они упрощают чтение запроса. Я бы подумал только о временной таблице для пакетного процесса, который действительно должен обрабатывать каждую запись. Даже тогда afaik это не рекомендуется, потому что на временной таблице для базы данных намного сложнее помочь вам с кешированием и индексами. Возможно, было бы лучше иметь постоянную таблицу с полем PK, уникальным для вашей транзакции.
Я должен признать, что мой опыт в основном связан с DB2, поэтому я предполагаю, что CTE работает аналогично в обоих продуктах. Я с радостью буду исправляться, если CTE каким-то образом уступает SQL-серверу. ;)